Error Logging/ Exception Handing

Error Logging

Since SQL Server 2005, we are able to handle T-SQL Errors using a Try Catch blog, there are multiple ways to show the error output based on the logic applied to raise an exception to show the user friendly message to the users instead of the SQL server error message. It’s always better to audit log all those errors into an error log table to store these errors at one place, easy to retrieve the information of error log as well.

Let’s see a basic error logging of where we can show detailed information of the error that’s caused by divide by zero error.  I am trying to show a simple error logging by capturing error_number, error_severity and the error_message and few other error properties.

Code for Example 2

-- ERROLOGGING

BEGIN TRY 

    -- Generate a divide-by-zero error. 

    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; 

GO 

Instead of writing all every time and getting redundancy, I prefer it to capture the same information by calling a stored procedure which does the same and capture the input parameters as well. So I have come up with a procedure which writes all this above information and saves a record to the audit table for showing all these errors at one place.  For these to be done we need an errorlog table created in the database to save these records. Please see below sample code for execution sql.

DECLARE                                              @i int =1, @j int =0

-- ERROLOGGING

BEGIN TRY

                DECLARE @Return int,

                                @Message varchar(1000) = 'Failed to run the query, see errorlog table',

                                @ParamList varchar(max)

='@i= '+CASE WHEN @i IS NOT NULL THEN  CAST(@i  as varchar)ELSE 'default' END+','+                                   '@j= '+CASE WHEN @j IS NOT NULL THEN  CAST(@j as varchar) ELSE 'default' END

                     -- Generate a divide-by-zero error. 

    SELECT @i/@j; 

END TRY 

BEGIN CATCH 

                EXECUTE [dbo].[LogError] @ParamList;

                SELECT @Return =  ERROR_NUMBER()

                                PRINT ERROR_NUMBER()

                                IF @Return = 50000 SET @Message =  ERROR_MESSAGE()

                                ELSE IF @Return = 515 SET @Message = 'A field required to save the record was not supplied'

                                RAISERROR(@Message,16,1) WITH SETERROR

END CATCH;

GO 

select * from dbo.errorlog

Comparing the level of Error logging and ease of information side by side.

--Error Log Table

CREATE TABLE [dbo].[ErrorLog](

                [ErrorLogID] [int] IDENTITY(1,1) NOT NULL,

                [ErrorTime] [datetime] NOT NULL CONSTRAINT [DF_ErrorLog_ErrorTime]  DEFAULT (getutcdate()) ,

                [UserName] [sysname] NOT NULL,

                [ErrorNumber] [int] NOT NULL,

                [ErrorSeverity] [int] NULL,

                [ErrorState] [int] NULL,

                [ErrorProcedure] [nvarchar](126) NULL,

                [ErrorLine] [int] NULL,

                [ErrorMessage] [nvarchar](4000) NOT NULL,

                [SQlCommand] [varchar](max) NULL,

 CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY CLUSTERED

(

                [ErrorLogID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

--Print Error

CREATE PROCEDURE [dbo].[PrintError]

AS

BEGIN

    SET NOCOUNT ON;

    -- Print error information.

    PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) +

          ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +

          ', State ' + CONVERT(varchar(5), ERROR_STATE()) +

          ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') +

          ', Line ' + CONVERT(varchar(5), ERROR_LINE());

    PRINT ERROR_MESSAGE();

END;

--Log Error

CREATE PROCEDURE [dbo].[LogError]

                @SQLCommand varchar(max) = NULL,

    @ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted

AS                              

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 LogError in order to successfully log error information.';

            RETURN;

        END

        INSERT [dbo].[ErrorLog]

            (

            [UserName],

            [ErrorNumber],

            [ErrorSeverity],

            [ErrorState],

            [ErrorProcedure],

            [ErrorLine],

            [ErrorMessage],

            [SqlCommand]

            )

        VALUES

            (

            CONVERT(sysname, CURRENT_USER),

            ERROR_NUMBER(),

            ERROR_SEVERITY(),

            ERROR_STATE(),

            ERROR_PROCEDURE(),

            ERROR_LINE(),

            ERROR_MESSAGE(),

            @SQLCommand

            );

        -- Pass back the ErrorLogID of the row inserted

        SET @ErrorLogID = @@IDENTITY;

    END TRY

    BEGIN CATCH

        PRINT 'An error occurred in stored procedure LogError: ';

        EXECUTE [dbo].[PrintError];

        RETURN -1;

    END CATCH

END;