Error handling for TSQL that is similar to the exception handling in .Net languages.
A group of SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is moved to the statements that are enclosed in a CATCH block.
Try Catch Syntax |
Copy Code
|
---|---|
BEGIN TRY { sql_statement | statement_block } END TRY BEGIN CATCH [ { sql_statement | statement_block } ] END CATCH |
Within a CATCH statement you can examine the @@ERROR variable to determine the error that occurred. This code will be the VistaDB error number associated with the error.
Try Catch Example |
Copy Code
|
---|---|
BEGIN TRY -- BUSINESS LOGIC -- IT DETECTS A BAD STATE RAISERROR( 5000, 1, 1) END TRY BEGIN CATCH IF @@ERROR = 5000 PRINT 'Got Error 5000' ELSE PRINT @@Error END CATCH |
In general we recommend that you only use TRY CATCH in situations where common errors frequently occur in the SQL. This should not normally be the case. You should let the errors go through to the application and have the application deal with the error.
Catch blocks in SQL script are very slow as they must be trapped internally and then the CATCH block must be loaded as a new script to be run.
ERROR_MESSAGE(), ERROR_LINE(), ERROR_PROCEDURE(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_NUMBER() are not supported.
@@ERROR is functionally the same as ERROR_NUMBER().