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.

BEGIN TRY
     { sql_statement | statement_block }
END TRY
BEGIN CATCH
     [ { sql_statement | statement_block } ]
END CATCH

Retrieving Error Information

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.

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

Catch in SQL or at Application?

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.

Unsupported Error Functions

ERROR_MESSAGE(), ERROR_LINE(), ERROR_PROCEDURE(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_NUMBER() are not supported.

@@ERROR is functionally the same as ERROR_NUMBER().