Template to Handle Error in SQL Server

Introduction

Below is the error handling template that we can use to manage error from SQL server 2005 onwards. Before jumping to template, let us first discuss few terms that will be used later in the tip. To know the basics of SQL server error, please visit my first article SQL server error details.

Background

@@TRANCOUNT

It returns the number of active transactions in the current session. It can be used to find the level of nesting of transaction.
BEGIN TRAN statement increments the transaction count by 1.
COMMIT TRAN statement decrements the transaction count by 1.
ROLLBACK TRAN resets the transaction count to 0.
It cannot be used to check if the current transaction can be commited or not.

SAVE TRANSACTION

It allows a mechanism to rollback a portion of a transaction. It will help us to commit a part of a transaction out of a large batch of script when something fails. It does not affect the @@TRANCOUNT value. But while using Rollback, we need to specify the Save Point name. If no save point name is specified, it will rollback all transactions. ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT value.

XACT_STATE

Introduced in SQL server 2005. XACT_STATE() is a scalar function. It gives us information about the current state of a request. It also says if the transaction is capable of being committed or not. But it cannot be used to determine nested transaction. It returns 3 values:

• 1: The current request has an active user transaction.The transaction can be committed.
• 0: There is no active transaction.
  
• -1: The current request has an active user transaction, but some error occurred and transaction cannot be committed.

TRY--CATCH

It is provided with SQL server 2005 to handle error as we do in other programming languages like C# . But here we don't have FINALLY block.
If any error is raised in TRY block, control will be passed to CATCH block.

Error Handling in Template

We can use try catch to handle error as below:

BEGIN TRY
 -- Code goes here
END TRY
BEGIN CATCH
  ---Error handling code goes here
END CATCH

Below is the complete template that can be used to handle error:

CREATE PROCEDURE [USP_Procedure_Name]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @TranCountAtStart INT;
    SET @TranCountAtStart = @@TRANCOUNT;

    BEGIN TRY
        IF @TranCountAtStart = 0
            BEGIN TRANSACTION
        ELSE
            SAVE TRANSACTION USP_Procedure_Name;

            -- put code here

        IF @TranCountAtStart = 0
            COMMIT;
    END TRY

    BEGIN CATCH
    
    DECLARE @ERRORMESSAGE    NVARCHAR(512),
            @ERRORSEVERITY    INT,
            @ERRORNUMBER    INT,
            @ERRORSTATE        INT,
            @ERRORPROCEDURE    SYSNAME,
            @ERRORLINE        INT

    SELECT
            @ERRORMESSAGE    = ERROR_MESSAGE(),
            @ERRORSEVERITY    = ERROR_SEVERITY(),
            @ERRORNUMBER    = ERROR_NUMBER(),
            @ERRORSTATE        = ERROR_STATE(),
            @ERRORPROCEDURE    = ERROR_PROCEDURE(),
            @ERRORLINE        = ERROR_LINE()

    SET @ERRORMESSAGE = 
    (
    SELECT  
      'Error:'        +    convert(nvarchar(50),@ErrorNumber) + space(1) +
      'Severity:'        +    convert(nvarchar(50),@ErrorSeverity) + space(1) +
      'State:'        +    convert(nvarchar(50),@ErrorState) + space(1) +
      'Routine_Name: '    +    isnull(@ErrorProcedure,'') + space(1) +
      'Line:'        +    convert(nvarchar(50),@ErrorLine) + space(1) +
      'Message: '        +    @ErrorMessage + space(1) +
      'ExecutedAs:'    +    SYSTEM_USER + space(1) +
      'Database:'        +    DB_NAME() + space(1) +
      'OSTime:'        +    convert(nvarchar(25),CURRENT_TIMESTAMP,121)
    )

            IF @TranCountAtStart = 0
            ROLLBACK TRANSACTION

            IF  @TranCountAtStart > 0
            ROLLBACK TRANSACTION USP_Procedure_Name;
            --We can also save the error details to a table for later reference here.
            RAISERROR (@ERRORMESSAGE,16,1)
    END CATCH
END

If you are using SQL server 2012 or above, then you can use the below template:

CREATE PROCEDURE [USP_Procedure_Name]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @TranCountAtStart INT;
    SET @TranCountAtStart = @@TRANCOUNT;

    BEGIN TRY
        IF @TranCountAtStart = 0
            BEGIN TRANSACTION
        ELSE
            SAVE TRANSACTION USP_Procedure_Name;

            -- put code here

        IF @TranCountAtStart = 0
            COMMIT;
    END TRY

    BEGIN CATCH
    
    DECLARE @ERRORMESSAGE    NVARCHAR(512),
            @ERRORSEVERITY    INT,
            @ERRORNUMBER    INT,
            @ERRORSTATE        INT,
            @ERRORPROCEDURE    SYSNAME,
            @ERRORLINE        INT,
            @XASTATE INT
    SELECT
            @ERRORMESSAGE    = ERROR_MESSAGE(),
            @ERRORSEVERITY    = ERROR_SEVERITY(),
            @ERRORNUMBER    = ERROR_NUMBER(),
            @ERRORSTATE        = ERROR_STATE(),
            @ERRORPROCEDURE    = ERROR_PROCEDURE(),
            @ERRORLINE        = ERROR_LINE()

    SET @ERRORMESSAGE = 
    (
    SELECT  
      'Error:'        +    convert(nvarchar(50),@ErrorNumber) + space(1) +
      'Severity:'        +    convert(nvarchar(50),@ErrorSeverity) + space(1) +
      'State:'        +    convert(nvarchar(50),@ErrorState) + space(1) +
      'Routine_Name: '    +    isnull(@ErrorProcedure,'') + space(1) +
      'Line:'        +    convert(nvarchar(50),@ErrorLine) + space(1) +
      'Message: '        +    @ErrorMessage + space(1) +
      'ExecutedAs:'    +    SYSTEM_USER + space(1) +
      'Database:'        +    DB_NAME() + space(1) +
      'OSTime:'        +    convert(nvarchar(25),CURRENT_TIMESTAMP,121)
    )

        SELECT @XASTATE = XACT_STATE();

        IF @XASTATE = - 1
            ROLLBACK;

        IF @XASTATE = 1
            AND @TranCountAtStart = 0
            ROLLBACK

        IF @XASTATE = 1
            AND @TranCountAtStart > 0
            ROLLBACK TRANSACTION USP_Procedure_Name;
            --We can also save the error details to a table for later reference here.
            RAISERROR (@ERRORMESSAGE,16,1)
    END CATCH
END

These can be used to manage nested transactions as well.

Points of Interest

You can check the correctness and use it in your development. Suggestions and feedback are highly welcomed.

History

• 30^th September, 2014: Initial version