Exception Handling and Error logging in TSQL

by RamiVemula · December 11, 2012

Most of the times it would be tricky on how to catch exceptions of different types such as business errors, data sanitation errors and logical errors etc. In this small tutorial I am planning to show how to capture different types of errors and log them in to a error table for further analysis. Alongside there will be a delegation of all kinds of errors to user intuitive messages, so that there will not be any schema exposure due to errors.

Check out following code, in which there are two scenarios which are capable of raising exceptions. We use TRY…CATCH block to capture error, then use “uspErrorStore” stored procedure to insert all error details along with other useful information about connection into the error table. Once stored, we shed off more meaningful exception using THROW to the caller, so that no schema is exposed. I personally use THROW error rather than making a select statement and sending results back to the caller, I thought this approach would be more apt in conveying that it is a SqlException rather than a ResultSet. And also we can use a function to consolidate most of the generic code in the CATCH block, but I want to present only the basic structure. Code refactoring can be done at any level, once we get the main requirement done.

Please check below code, which is self explanatory and there are also necessary comments where ever required -

USE [TestDB]
GO


/* Drop and Recreate Table */

IF EXISTS(SELECT * FROM sys.sysobjects WHERE type = 'U' AND name = 'TestTable')
BEGIN
    DROP TABLE [dbo].[TestTable];
END
GO


CREATE TABLE [dbo].[TestTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    CONSTRAINT pk_Id PRIMARY KEY (Id)
)
GO


/* Create Error Table */

IF NOT EXISTS(SELECT * FROM sys.sysobjects WHERE type = 'U' AND name = 'ErrorTable')
BEGIN
    CREATE TABLE [dbo].[ErrorTable](
    [ErrorId] [int] IDENTITY(1,1) NOT NULL,
    [Error_Number] [int] NOT NULL,
    [Custom_Type] [nvarchar](64) NOT NULL,
    [Error_Severity] [int] NOT NULL,
    [Error_State] [int] NOT NULL,
    [Error_Procedure] [nvarchar](128) NOT NULL,
    [Error_Line] [int] NOT NULL,
    [Error_Message] [nvarchar](4000) NOT NULL,
    [Database_User] [nvarchar](256) NOT NULL,
    [Original_Login] [nvarchar](128) NOT NULL,
    [Context_Login] [nvarchar](128) NOT NULL,
    [InsertedDate] [datetime] NOT NULL,
    CONSTRAINT pk_ErrorId PRIMARY KEY CLUSTERED (ErrorId)
)
END
GO



/* Drop and Recreate Error Stored Procedure */

IF EXISTS(SELECT * FROM sys.sysobjects WHERE type = 'P' AND name = 'uspErrorStore')
BEGIN
    DROP PROCEDURE [dbo].[uspErrorStore];
END
GO

CREATE PROCEDURE [dbo].[uspErrorStore]
(
        @Error_Number [int],
        @Custom_Type [nvarchar](64),
        @Error_Severity [int],
        @Error_State [int],
        @Error_Procedure [nvarchar](128),
        @Error_Line [int],
        @Error_Message [nvarchar](4000)
)
AS
BEGIN

    SET NOCOUNT ON;
    BEGIN
    
        -- Insert into Error Table
        INSERT INTO [dbo].[ErrorTable]
        (
            [Error_Number],
            [Custom_Type],
            [Error_Severity],
            [Error_State],
            [Error_Procedure],
            [Error_Line],
            [Error_Message],
            [Database_User],
            [Original_Login],
            [Context_Login],
            [InsertedDate]
        )
        VALUES
        (
            @Error_Number,
            @Custom_Type,
            @Error_Severity,
            @Error_State,
            @Error_Procedure,
            @Error_Line,
            @Error_Message,
            USER_NAME(),
            ORIGINAL_LOGIN(),
            SUSER_SNAME(),
            GETDATE()
        )

    END
END
GO


/* Drop and Recreate Error Stored Procedure */

IF EXISTS(SELECT * FROM sys.sysobjects WHERE type = 'P' AND name = 'TestTableProcedure')
BEGIN
    DROP PROCEDURE [dbo].[TestTableProcedure];
END
GO

CREATE PROCEDURE [dbo].[TestTableProcedure]
(
    @TestInput [int]
)
AS
BEGIN

    SET NOCOUNT ON;
    /*
        When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, 
        the entire transaction is terminated and rolled back. When SET XACT_ABORT is OFF, 
        in some cases only the Transact-SQL statement that raised the error is rolled 
        back and the transaction continues processing. Depending upon the severity of the 
        error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. 
        OFF is the default setting.
    */
    SET XACT_ABORT OFF;

    BEGIN TRAN
    BEGIN TRY

        IF @TestInput = 1 
            BEGIN
                -- This insert fails as we cannot supply explicit value for identity column
                INSERT INTO [dbo].[TestTable](Id, Name) VALUES(1,'Rami');
            END
        ELSE
            BEGIN
                -- Raise custom business exception sample 
                RAISERROR ('Custom Business Error', 16,    1);
            END

    COMMIT TRAN
    END TRY
    

    BEGIN CATCH
    DECLARE
        -- Get all exception details
        @Error_Number [int] = ERROR_NUMBER(),
        @Custom_Type [nvarchar](64),
        @Error_Severity [int] = ERROR_SEVERITY(),
        @Error_State [int]  = ERROR_STATE(),
        @Error_Procedure [nvarchar](128)  = ERROR_PROCEDURE(),
        @Error_Line [int]  = ERROR_LINE(),
        @Error_Message [nvarchar](4000) = ERROR_MESSAGE()


        -- If the Error number is user defined then set the type as Business
        -- or else set the type to be System.
        IF @Error_Number >= 50000
            SET @Custom_Type = 'Business'
        ELSE
            SET @Custom_Type = 'System'


        -- IF XACT_ABORT() set to OFF, then based on severity we
        -- can either rollback or commit transactions. -1 tells us
        -- to rollback, 1 tell us to commit and 0 says there are no
        -- transactions.
        IF (XACT_STATE()) = -1
        BEGIN
            ROLLBACK TRANSACTION;
        END;

        IF (XACT_STATE()) = 1
        BEGIN
            COMMIT TRANSACTION;   
        END;


        -- Insert into Error Table
        EXEC [dbo].[uspErrorStore]  @Error_Number,
                            @Custom_Type,
                            @Error_Severity,
                            @Error_State,
                            @Error_Procedure,
                            @Error_Line,
                            @Error_Message 


        /*
            Send Normal messages to user, rather than a detailed error message 
            with schema exposure. Here I would use a switch case identify which
            type of error and give appropriate logical user messages, rather than
            exposing system generated schema messages.
        */
        IF @Custom_Type = 'Business'
            THROW;
        ELSE
            THROW 99999, 'Check Error Log for more details.', @Error_State;
        
    END CATCH
END
GO

/* Execute Stored Procedure */
-- Procedural call for first exception raise
EXEC [dbo].[TestTableProcedure] 1
GO
-- Procedural call for second exception raise
EXEC [dbo].[TestTableProcedure] 2
GO

/* Display Results */
SELECT * FROM [dbo].[TestTable];
SELECT * FROM [dbo].[ErrorTable];