Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server / SQL-Server-2012

Template to Handle Error in SQL Server

4.91/5 (8 votes)
29 Sep 2014CPOL2 min read 25.5K  
Below is one 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:

SQL
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:

SQL
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:

SQL
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

  • 30th September, 2014: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)