When working with SQL Server, it is important to have a robust error handling mechanism in place to ensure that any unexpected errors are properly handled. In this article, we will explore the limitations of the Try-Catch block in SQL Server and discuss a solution to handle errors more effectively.

The Limitations of Try-Catch

By default, the Try-Catch block in SQL Server can handle errors with a severity of 20 or higher, as long as the connection is not closed. However, errors with a severity of 10 or lower are considered warnings or informational messages and are not handled by the Try-Catch block. Additionally, if an error with a severity of 20 or higher causes the database engine to close the connection, it will not be handled by the Try-Catch block.

This can lead to situations where errors are not properly caught and handled, resulting in unexpected behavior and potential data inconsistencies.

A Common Issue

Let’s consider a scenario where we have a data modification procedure that performs actions like insert, update, or delete. We want to use a Try-Catch block to handle any errors that may occur during the execution of the procedure. However, we encounter a situation where the session ends, but the transaction stays open.

Here is an example code snippet that demonstrates this issue:

CREATE PROCEDURE USP_Test
AS
BEGIN
   BEGIN TRY
      BEGIN TRAN
         SELECT * FROM TEST1
         SELECT * FROM TEST
      COMMIT TRAN
   END TRY
   BEGIN CATCH
      PRINT XACT_STATE()
      PRINT error_message()
      PRINT ERROR_SEVERITY()

      IF (XACT_STATE()) = -1
      BEGIN
         ROLLBACK TRANSACTION;
      END;
   END CATCH
END

When executing this stored procedure, we encounter errors because the table “TEST” does not exist. However, the Try-Catch block is unable to catch these errors, and the transaction remains open even after the session ends.

Resolving the Issue

To address this issue, we can use the “SET XACT_ABORT ON” statement. This statement ensures that if a run-time error occurs, SQL Server will rollback the entire transaction and abort the T-SQL batch.

Here is an updated version of the stored procedure with the “SET XACT_ABORT ON” statement:

CREATE PROCEDURE USP_Test
AS
SET XACT_ABORT ON
BEGIN
   BEGIN TRY
      BEGIN TRAN
         SELECT * FROM TEST1
         SELECT * FROM TEST
      COMMIT TRAN
   END TRY
   BEGIN CATCH
      PRINT XACT_STATE()
      PRINT error_message()
      PRINT ERROR_SEVERITY()

      IF (XACT_STATE()) = -1
      BEGIN
         ROLLBACK TRANSACTION;
      END;
   END CATCH
END

By adding this statement, we ensure that any errors that occur during the execution of the stored procedure are properly caught and handled, and the transaction is rolled back if necessary.

Conclusion

When working with transactions in SQL Server, it is important to have a robust error handling mechanism in place. While the Try-Catch block is a useful tool, it has its limitations. By using the “SET XACT_ABORT ON” statement, we can ensure that any run-time errors are properly handled and the transaction is rolled back if necessary. I highly recommend using “SET XACT_ABORT ON” whenever you work with transactions in SQL Server to ensure data integrity and consistency.