TRY…CATCH and ERROR Handling-Sql Server 2005

Syntax:
BEGIN TRY
{ sql_statement |
statement_block }
END TRY
BEGIN CATCH
{ sql_statement |
statement_block }
END CATCH



The TRY or CATCH block can contain a single T-SQL statement or a series of statements. The CATCH block must follow immediately after the TRY block. The TRY/CATCH block cannot span more than a single batch. In addition, TRY/CATCH block cannot span an IF/ELSE statement.

Example of TRY…CATCH:
BEGIN TRY
DECLARE @X INT
---- Divide by zero to generate Error
SET @X = 1/0
PRINT 'Command after error in TRY block'
END TRY
BEGIN CATCH
PRINT 'Error Detected'
END CATCH
PRINT 'Command after TRY/CATCH blocks'
Above code will return following result:

Error Detected
Command after TRY/CATCH blocks

If all the statements within the TRY block are executed successfully, then processing does not enter the CATCH block, but instead skips over the CATCH block and executes the first statement following the END CATCH statement. Removing SET statement in above code PRINT ‘Error Detected’ statement is not executed, but the PRINT statement within the TRY block is executed, as well as the PRINT statement after the TRY/CATCH block. TRY/CATCH blocks can be nested.

Limitation of TRY…CATCH:

• Compiled errors are not caught.
• Deferred name resolution errors created by statement level recompilations. (If process is terminated by Kill commands or broken client connections TRY…CATCH will be not effective)
• Errors with a severity greater than 10 that do not terminate their database connection are caught in the TRY/CATCH block.

For errors that are not trapped, SQL Server 2005 passes control back to the application immediately, without executing any CATCH block code.

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.

You can use these functions anywhere inside a CATCH block, and they will return information regarding the error that has occurred. These functions will return the value null outside of the CATCH block.

Similar example of TRY…CATCH which includes all the ERROR functions:
USE AdventureWorks;
GO
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO