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:
-- sql statements which are potential source of errors
-- in case error occurs it will be handled here
Below is the description of error functions used within CATCH block
It is the error number and its value is same as returned by @@ERROR function.
This provides the line number of the batch or stored procedure where the error occurred.
This returns the severity level of the error.
This returns the state number of the error.
This returns the name of the stored procedure or trigger where the error occurred.
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
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;
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.