September 4, 2010

SQL Try Catch

What is TRY… CATCH?

The TRY... Catch construct was designed to improve the functionality in processing errors. With it comes a cleaner and more readable syntax that is familiar to programmers. In addition, this construct can return the transaction state of your procedures, and allow the developer to either log the details of the error, or return this information to the calling procedure.

The new functions available with TRY... CATCH include XACT_STATE, ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, and ERROR_STATE.

@@ERROR vs. TRY... CATCH

Error handling in SQL Server 2000 and earlier consisted of the @@ERROR function. SQL Server 2005 introduced the TRY... Catch construct. These two error handling styles have varying ways of dealing with the errors produced.

• @@ERROR is cleared and reset on each statement executed. This requires developers to test the error, or save it to a variable, after each TSQL statement. This greatly increases the lines of code needed.

• TRY…CATCH constructs contain the error handling in the CATCH block, which produces a cleaner layout. It also contains an expanded set of system functions for returning error codes and messages.


Pro’s of TRY… CATCH

Catch blocks can be nested.

Better able to handle deadlocks.

Error messages are defined and stored in the system table sys.messages. The sys.messages catalog can return a system-defined message, or developers can create their own user-defined message (see sp_addmessage).

Outside of a CATCH block, @@ERROR is the only part of a Database Engine error available within the batch, stored procedure, or trigger that generated the error. All other parts of the error, such as its severity, state, and message text containing replacement strings (object names, for example) are returned only to the application in which they can be processed using the API error-handling mechanisms. If the error invokes a

CATCH block, the system functions ERROR_LINE, ERROR_MESSAGE, ERROR_PROCEDURE, ERROR_NUMBER, ERROR_SEVERITY, and ERROR_STATE can be used.

No comments:

Post a Comment