Before SQL Server 2005, the only practical way to trap errors in SQL was using the old-fashioned @@error system variable. Although this is still supported, in this blog we will learn how to use TRY-CATCH for handling errors.TRY-CATCH block is similar to the one which we have in C#, but it doesnt have the FINALLY block. If any error occurs in the statements enclosed in the TRY block then the control is immediately passed to the associated CATCH block.

Below is the structure if the TRY-CATCH block:

BEGIN TRY
-- sql statements which are potential source of errors
END TRY 
BEGIN CATCH
-- in case error occurs it will be handled here
END CATCH

Below is the description of error functions used within CATCH block

1) ERROR_NUMBER()

It is the error number and its value is same as returned by @@ERROR function.

2) ERROR_LINE()

This provides the line number of the batch or stored procedure where the error occurred.

3) ERROR_SEVERITY()

This returns the severity level of the error.

4) ERROR_STATE()

This returns the state number of the error.

5) ERROR_PROCEDURE()

This returns the name of the stored procedure or trigger where the error occurred.

6) ERROR_MESSAGE()

The complete text of the error message.The text includes the values supplied for any substitute parameters, such as lengths, object names, or times.

Running the below proc will provide details for divide by zero error.

CREATE PROCEDURE uspTryCatch
AS
BEGIN TRY
    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

Following are points worth noting regarding TRY-CATCH usage:

1) TRY..CATCH block combination catches errors whose severity lies between 11 and 19.

2) CATCH block is executed only if there is an error in T-SQL statements within TRY block.

3) Each CATCH block is associated with only one TRY block and vice versa.

4) Inside the TRY..CATCH block XACT_STATE function can be used to check whether an open transaction is committed or not. It will return -1 if transaction is not committed else it will return 1.