T-SQL Programming – Stored procedure for logging Error(s) in the ErrorLog table

Error handling is an important aspect of any programming language. Like other programming languages, in T-SQL programming also, we use the TRY_CATCH block to capture the error.  To capture the detailed error, we use the system defined function provided by SQL Server. In order to reduce the rewriting of same code & utilizing the code reusability. We can use the below stored procedure in the catch block to catch the error in the stored procedure and stored it into a separate ErrorLog table for reference .

USE [MyDatabase]

GO

/****** Object:  StoredProcedure [dbo].[uspLogError]    Script Date: 8/17/2018 6:54:04 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- usp_LogError stored procedure 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].[usp_LogError]

    @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 currently transaction state is uncommittable. '

                + '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;

GO