Error handling in SQL Server using TRY CATCH

TSQL supports error handing using standard Try Catch block from SQL 2005 onwards. This syntax and features of this construct are similar to any standard .net language.

Below points are vital to note while we use TRY CATCH block:

1. A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection.
2. A TRY block must be immediately followed by an associated CATCH block. Including any other statements between the END TRY and BEGIN CATCH statements generates a syntax error.
3. A TRY…CATCH construct cannot span multiple blocks of Transact-SQL statements. For example, a TRY…CATCH construct cannot span two BEGIN…END blocks of Transact-SQL statements and cannot span an IF…ELSE construct.

If there are no errors in the code that is enclosed in a TRY block, when the last statement in the TRY block has finished running, control passes to the statement immediately after the associated END CATCH statement. If there is an error in the code that is enclosed in a TRY block, control passes to the first statement in the associated CATCH block.

Example Syntax:

BEGIN TRY

[… Your code …]

END TRY

BEGIN CATCH

     [… Your code … ]

END CATCH

In the scope of a CATCH block, the following system functions can be used to obtain information about the error that caused the CATCH block to be executed:

·         ERROR_NUMBER() returns the number of the error.

·         ERROR_SEVERITY() returns the severity.

·         ERROR_STATE() returns the error state number.

·         ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.

·         ERROR_LINE() returns the line number inside the routine that caused the error.

·         ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

These functions return NULL if they are called outside the scope of the CATCH block.


Example (Pseudo code):

BEGIN TRY -- Begining of Try Block

-- Start of code.

SELECT GETDATE()-- This will execute

SELECT 1/0 -- Here control will go to CATCH block as it is error

print 'I am not here' -- It will not execute as control already found error

-- End of code.

END TRY -- End of Try Block

BEGIN CATCH -- Start of CATCH Block

--Start of Error Handler

SELECT 'Error!!!  ' + ERROR_MESSAGE() -- Error caught including error message printed

--End of Error Handler

END CATCH; -- End of Catch Block

For more details, please visit at http://msdn.microsoft.com/en-us/library/ms175976.aspx