SQL SERVER –TRY…CATCH and ERROR HANDLING

Like in most modern programming languages, you put your code in the TRY block and you check for the errors in the CATCH block. TRY/CATCH helps to write logic separate the action and error handling code. The code meant for the action is enclosed in the TRY block and the code for error handling is enclosed in the CATCH block. In case the code within the TRY block fails, the control automatically jumps to the CATCH block, letting the transaction roll back and resume execution. In addition to this, the CATCH block captures and provides error information that shows you the ID, message text, state, severity and transaction state of an error.

Functions to be used in CATCH block are :

• ERROR_NUMBER: returns the error number, and is the same value of @@ERROR.
• ERROR_SEVERITY: returns the severity level of the error that invoked the CATCH block.
• ERROR_STATE: returns the state number of the error.
• ERROR_LINE: returns the line number where the error occurred.
• ERROR_PROCEDURE: returns the name of the stored procedure or trigger for which the error occurred.
• ERROR_MESSAGE: returns the full message text of the error. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

Limitation of  TRY…CATCH:

• A TRY block must be followed immediately by a CATCH block.
• TRY…CATCH constructs can be nested, which means that TRY…CATCH constructs can be placed inside other TRY and CATCH blocks. When an error occurs within a nested TRY block, program control is transferred to the CATCH block associated with the nested TRY block.
• To handle an error that occurs within a given CATCH block, write a TRY…...CATCH block within the specified CATCH block.
• Errors with a severity of 20 or higher that cause the Database Engine to terminate the connection will not be handled by the TRY…CATCH block. However, TRY…CATCH will handle errors with a severity of 20 or higher as long as the connection is not terminated.
• Errors with a severity of 10 or lower are considered warnings or informational messages, and are not handled by TRY…CATCH blocks.
• Try & Catch block will work for Errors with a severity of 10 < TRY & Block <= Errors with a severity of 20.
• GOTO statements cannot be used to enter a TRY or CATCH block. GOTO statements can be used to jump to a label inside the same TRY or CATCH block or to leave a TRY or CATCH block.

Example:

Using TRY…CATCH in a transaction:

About Author:

Riddhi Solanki is enthusiast .net developer who works as associate consultant with Systems Plus Pvt. Ltd. She in free time reads and write on various web technologies. She can be contacted at:riddhi.solanki@spluspl.com