How to do Error Handling with TRY CATCH in SQL Server

Error Handling with TRY CATCH RAISEERROR () THROW in SQL Server
The process of handling errors is essential in any Query/Programming language. Generally we have to implement safe error handling in our T-SQL if we want to build solid SQL Server code.
From SQL Server 2005, and later versions, superseded the old style @@Error method of error handling, with the TRY…CATCH blocks that are more familiar to Java and C# languages.
Since the release of SQL Server 2005, We have been able to handle errors in your T-SQL code by including a TRY…CATCH block that controls the flow of your script when an error occurs, similar to how procedural languages have traditionally handled errors.

The TRY…CATCH block makes it easy to return or audit error-related data, as well as take other actions. 

Any error that occurs in Transactions defined in the BEGIN TRY.. END TRY will be handled by the immediate BEGIN CATCH..END CATCH block in which we can define RAISERROR statement in order to re-throw error-related data to the calling application. 

However, with the release of SQL Server 2012, you now have a replacement for RAISERROR, the THROW statement, which makes it easier than ever to capture the error-related data.

Scenario:

Suppose we have a Sales Transaction table, in which the Transaction details will be update, insert by the Sales agents when ever required.

The data in the table "[dbo].[SalesTrans]" as follows..

Now we will define check constraint on the 'Gross_Sales' field of this table, to ensure no negative bookings value can be inserted/updated to this field.

ALTER TABLE [dbo].[SalesTrans]
ADD CONSTRAINT ck_GrossSales CHECK (Gross_Sales >= 0);

Since we have added a check constraint on this table to ensure Gross_Sales>=0, one can insert or update only values >=0.

If any one enters negative values, then it should throw an error and that error we have to handle by capturing and throwing an error message details using TRY.. CATCH method, which is our object here.

Now lets create a simple Procedure for updating this Table..

CREATE PROCEDURE [dbo].[sp_UpdateSalesTrans]
  @SalesOrderID INT,
  @SalesAmt MONEY = 0
AS
BEGIN
  BEGIN TRY
    BEGIN TRANSACTION
      UPDATE [dbo].[SalesTrans] SET Gross_Sales = @SalesAmt
      WHERE SalesOrder_Id = @SalesOrderID;
    COMMIT TRANSACTION
  END TRY
  BEGIN CATCH

    IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION

    DECLARE @ErrorNumber INT = ERROR_NUMBER();
    DECLARE @ErrorLine INT = ERROR_LINE();
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();
     PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));
    PRINT 'Actual error line number: ' + CAST(@ErrorLine AS VARCHAR(10));
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
  END CATCH
  Select * From [dbo].[SalesTrans] WHERE SalesOrder_Id = @SalesOrderID;
END

GO

Notes:

In the BEGIN TRY..END TRY block we defined Transaction for Update. If any error occurs in that block, immediately it will exit from that block and jump to the corresponding BEGIN CATCH..END CATCH block next to it. 

In the CATCH block first thing we defined to roll back the transaction if it is still running. We can do this using the @@TRANCOUNT function to determine whether any transactions are still open. @@TRANCOUNT is a built-in SQL Server function that returns the number of running transactions in the current session. In this case, there should be only one (if an error occurs), so we can roll back that transaction.

Next, we declared a set of variables based on system functions that SQL Server makes available within the scope of the CATCH block. The functions return error-related information that you can reference in your T-SQL statements. Currently, SQL Server supports the following functions for this purpose:
ERROR_NUMBER(): The number assigned to the error.
ERROR_LINE(): The line number inside the routine that caused the error.
ERROR_MESSAGE(): The error description text, which includes the values supplied for any substitutable parameters, such as times or object names.
ERROR_SEVERITY(): The error’s severity.
ERROR_STATE(): The error’s state number.
ERROR_PROCEDURE(): The name of the stored procedure or trigger that generated the error.

Next we included two PRINT statements that display the values of the @ErrorNumber and @ErrorLine variables. The reason we do this is to see the actual error values which are different from the values return by RAISERROR statement.

The RAISERROR statement comes after the PRINT statements. The statement returns error information to the calling application. Generally, when using RAISERROR, we should include an error message, error severity level, and error state as arguments.

Now lets try to update amount as '987654.75' for a Sales Order Id '123456933' using the above Procedure.

Execute [dbo].[sp_UpdateSalesTrans] 123456933, 987654.75

We can update successfully as the sales amount is +ve(>=0) that passes the constraint.

Now again lets try update the same sales order id with a Negative sales amount, we should get the following error as it violating the check constraint rule.. 

Execute [dbo].[sp_UpdateSalesTrans] 123456933, -994565.75

(0 row(s) affected)
Actual error number: 547
Actual error line number: 8
Msg 50000, Level 16, State 0, Procedure sp_UpdateSalesTrans, Line 71

The UPDATE statement conflicted with the CHECK constraint "ck_GrossSales". 

The conflict occurred in database "AnalyticsDB", table "dbo.SalesTrans", column 'Gross_Sales'.

From this message, we clearly understood that there was an error, that actually occurred at the line number 8 (enable line numbers in your SSMS to see) in your procedure.

The error number 547 indicates the Check Constraint Conflict.

Now the procedure failed and the transaction was rolled back, and it will stop executing the further lines of code after CATCH block in your procedure.

If you wants to ignore this error, and executes the further lines of code (in our example we have the Select statement in our procedure), simply don't declare the Update Statement in the Transaction Block, and then specify a blank CATCH block without specifying any thing in side.

However, since we know that there a Transaction which has a failure chance, to avoid the following error, we need to keep the Rollback Transaction in the CATCH Block.

Msg 266, Level 16, State 2, Procedure sp_UpdateSalesTrans, Line 33
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

CREATE PROCEDURE [dbo].[sp_UpdateSalesTrans]
  @SalesOrderID INT,
  @SalesAmt MONEY = 0
AS
BEGIN
  BEGIN TRY
    BEGIN TRANSACTION
      UPDATE [dbo].[SalesTrans]
      SET Gross_Sales = @SalesAmt
      WHERE SalesOrder_Id = @SalesOrderID;
    COMMIT TRANSACTION
  END TRY
  BEGIN CATCH  
    IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
  END CATCH
  Select * From [dbo].[SalesTrans] WHERE SalesOrder_Id = @SalesOrderID;
END
GO

Now lets try again to execute Procedure by Negative values, then we will not get any error as well as Transaction also will not update as it is Rolled back.

Execute [dbo].[sp_UpdateSalesTrans] 123456933, -994565.75

Simplifying Error CATCH with THROW Statement:

To simplify returning errors in a CATCH block, SQL Server 2012 introduced the THROW statement. With the THROW statement, you don’t have to specify any parameters and the results are more accurate. You simply include the statement as is in the CATCH block.

CREATE PROCEDURE [dbo].[sp_UpdateSalesTrans]
  @SalesOrderID INT,
  @SalesAmt MONEY = 0
AS
BEGIN
  BEGIN TRY
    BEGIN TRANSACTION
      UPDATE [dbo].[SalesTrans] SET Gross_Sales = @SalesAmt
      WHERE SalesOrder_Id = @SalesOrderID;
    COMMIT TRANSACTION
  END TRY
  BEGIN CATCH

    IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION

    DECLARE @ErrorNumber INT = ERROR_NUMBER();
    DECLARE @ErrorLine INT = ERROR_LINE();

    PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));
    PRINT 'Actual error line number: ' + CAST(@ErrorLine AS VARCHAR(10));
  THROW;
  END CATCH
  Select * From [dbo].[SalesTrans] WHERE SalesOrder_Id = @SalesOrderID;
END
GO

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------