Error handling using TRY Catch in SQL Server

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

Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook