This post is a continuation of our error handling template from a previous post. It is always good to get another opinion of how to create an error handling template. Here is another template. It is from the website codeproject.com.

The code below can be used for versions of SQL Server that are 2012 or higher. The code below is NOT VERIFIED. Later on in this post we will make a few changes to this code. We will add our three stored procedures to the place where he says “your code here”. The second stored procedure will throw and error and we will see what happens.

Later on in this post we will modify the code a little bit to include our procedures that we’ve been using.

Here is the code from CodeProject.

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

Here is a bit of background information from that same web page at codeproject.com

@@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. To cancel an entire transaction, use the form ROLLBACK TRANSACTION transaction_name. All the statements or procedures of the transaction are undone. For more detail on SAVE TRANSACTION, try this article.

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 the transaction cannot be committed. The request cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction. The request cannot perform any write operations until it rolls back the transaction. The request can only perform read operations until it rolls back the transaction. After the transaction has been rolled back, the request can perform both read and write operations and can begin a new transaction.

TRY–CATCH

It is provided with SQL server 2005 and above to handle errors 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.

Testing the Template

CREATE PROCEDURE [CodeProjectErrTemplate]
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @TranCountAtStart INT;
    SET @TranCountAtStart = @@TRANCOUNT;
    BEGIN TRY
        IF @TranCountAtStart = 0 BEGIN TRANSACTION;
        ELSE SAVE TRANSACTION SaveTranAccountInsert;

	    -- our code is here -----------------
	    DELETE FROM dbo.Account;
	    EXEC InsertAccount1
	    EXEC InsertAccountDup  -- error!
	    EXEC InsertAccount2
	    -- end of our code ------------------
	
            IF @TranCountAtStart = 0 COMMIT TRANSACTION;
    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 TRANSACTION;
        IF @XASTATE = 1 AND @TranCountAtStart = 0 ROLLBACK TRANSACTION;
        IF @XASTATE = 1 AND @TranCountAtStart > 0 
            BEGIN
                ROLLBACK TRANSACTION SaveTranAccountInsert;
                --We can also save the error details to a table for later reference here.
                RAISERROR (@ERRORMESSAGE,16,1);
            END
    END CATCH
END