Error handling using TRY Catch was introduced in SQL Server 2005. If you have .net background then you must be familiar of using TRY – CATCH to handle errors. It is used in pretty much same was as in .Net. An example is shown below.
BEGIN TRY -- this will result in unique constraint violation INSERT INTO Person.AddressType VALUES('Billing',newid(),getdate()) END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_LINE() AS ErrorLine, ERROR_PROCEDURE() AS ErrorProcedure END CATCH
The above query inserts a duplicate value in Person. Address Type table. This results in unique key constraint violation error, which is captured by CATCH block as shown in below snapshot.
If you would look into Adventure Works 2014 database there is a procedure usp log Error which logs error into Error log table. The procedure text from the database is given below
USE [AdventureWorks2014] GO /****** Object: StoredProcedure [dbo].[uspLogError] Script Date: 10/5/2014 5:09:56 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- uspLogError logs error information in the ErrorLog table about the -- error that caused execution to jump to the CATCH block of a -- TRY...CATCH construct. This should be executed from within the scope -- of a CATCH block otherwise it will return without inserting error -- information. CREATE PROCEDURE [dbo].[uspLogError] @ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted AS -- by uspLogError in the ErrorLog table BEGIN SET NOCOUNT ON; -- Output parameter value of 0 indicates that error -- information was not logged SET @ErrorLogID = 0; BEGIN TRY -- Return if there is no error information to log IF ERROR_NUMBER() IS NULL RETURN; -- Return if inside an uncommittable transaction. -- Data insertion/modification is not allowed when -- a transaction is in an uncommittable state. IF XACT_STATE() = -1 BEGIN PRINT 'Cannot log error since the current transaction is in an uncommittable state. ' + 'Rollback the transaction before executing uspLogError in' + 'order to successfully log error information.'; RETURN; END INSERT [dbo].[ErrorLog] ( [UserName], [ErrorNumber], [ErrorSeverity], [ErrorState], [ErrorProcedure], [ErrorLine], [ErrorMessage] ) VALUES ( CONVERT(sysname, CURRENT_USER), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE() ); -- Pass back the ErrorLogID of the row inserted SET @ErrorLogID = @@IDENTITY; END TRY BEGIN CATCH PRINT 'An error occurred in stored procedure uspLogError: '; EXECUTE [dbo].[uspPrintError]; RETURN -1; END CATCH END;
The procedure logs error into Error log table when transaction is in commit table state else it displays a message. The procedure also prints the error using usp Print Error stored procedure. Let’s use this procedure to log an error in error log table.
As shown in above snapshot, usp Log Error procedure is executed in catch block, which inserts a row for the error in Error Log table.
Another use case for TRY-CATCH is re-running transactions involved in a deadlock. This is done by checking the error number for deadlock and then rerunning the transaction. The details can be found here
More information regarding TRY-CATCH can be found here