title: Using XACT_ABORT and TRY CATCH together in SQL Server break tSQLt Rollback
body: <p>I am beginning to work with tSQLt unit tests for SQL Server in my production code.  Currently, I use <a href="http://www.sommarskog.se/error_handling/Part1.html" rel="nofollow">Erland Sommarskog's</a> error handling pattern for SQL Server.</p>
  
  <pre><code>USE TempDB;
  
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  GO
  
  IF OBJECT_ID('dbo.SommarskogRollback') IS NOT NULL
    DROP PROCEDURE dbo.SommarskogRollback;
  GO
  
  CREATE PROCEDURE dbo.SommarskogRollback
  AS
  BEGIN; /*Stored Procedure*/
  
    SET XACT_ABORT, NOCOUNT ON;
  
    BEGIN TRY;
  
      BEGIN TRANSACTION;
  
        RAISERROR('This is just a test.  Had this been an actual error, we would have given you some cryptic gobbledygook.', 16, 1);
  
      COMMIT TRANSACTION;
  
    END TRY
    BEGIN CATCH;
  
      IF @@TRANCOUNT &gt; 0
        ROLLBACK TRANSACTION;
  
      THROW;
  
    END CATCH;
  
  END;   /*Stored Procedure*/
  GO
  </code></pre>
  
  <p>Erland Sommarskog recommends that we <em>always</em> SET XACT_ABORT ON, because only then does SQL Server handle errors in a (mostly) consistent manner.</p>
  
  <p>This creates a problem when using tSQLt, though.  tSQLt executes all tests inside an explicit transaction.  When the tests are complete the entire transaction rolls back.  This makes cleanup of the test artifacts completely painless. However, with XACT_ABORT ON, any error thrown inside a TRY block immediately <em>dooms</em> that transaction.  The transaction must roll back completely.  It cannot commit, and it cannot roll back to a save point.  In fact, nothing can write to the transaction log inside that session until the transaction rolls back.  However, tSQLt can't track the test results properly unless the transaction is open when the tests end.  tSQLt stops executing and throws a ROLLBACK ERROR for <em>doomed</em> transactions.  The test that failed shows a status of Error (rather than Success or Failure), and subsequent tests don't run.</p>
  
  <p>Sebastian Meine, the creator of tSQLt, recommends a different <a href="http://sqlity.net/en/585/how-to-rollback-in-procedures/" rel="nofollow">error handling pattern</a>.</p>
  
  <pre><code>USE TempDB;
  
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  GO
  
  IF OBJECT_ID('dbo.MeineRollback') IS NOT NULL
    DROP PROCEDURE dbo.MeineRollback;
  GO
  
  CREATE PROCEDURE dbo.MeineRollback
  AS
  BEGIN /*Stored Procedure*/
  
    SET NOCOUNT ON;
  
    /* We declare the error variables here, populate them inside the CATCH 
     * block and then do our error handling after exiting the CATCH block
     */
    DECLARE @ErrorNumber      INT
           ,@MessageTemplate  NVARCHAR(4000)
           ,@ErrorMessage     NVARCHAR(4000)
           ,@ErrorProcedure   NVARCHAR(126)
           ,@ErrorLine        INT
           ,@ErrorSeverity    INT
           ,@ErrorState       INT
           ,@RaisErrorState   INT
           ,@ErrorLineFeed    NCHAR(1) = CHAR(10)
           ,@ErrorStatus      INT = 0
           ,@SavepointName    VARCHAR(32) = REPLACE( (CAST(NEWID() AS VARCHAR(36))), '-', '');
           /*Savepoint names are 32 characters and must be unique.  UNIQUEIDs are 36, four of which are dashes.*/
  
    BEGIN TRANSACTION; /*If a transaction is already in progress, this just increments the transaction count*/
  
    SAVE TRANSACTION @SavepointName;
  
    BEGIN TRY;
  
      RAISERROR('This is a test.  Had this been an actual error, Sebastian would have given you a meaningful error message.', 16, 1);
  
    END TRY
    BEGIN CATCH;
  
      /* Build a message string with placeholders for the original error information
       * Note:  "%d" &amp; "%s" are placeholders (substitution parameters) which capture
       *        the values from the argument list of the original error message.
       */
      SET @MessageTemplate = N': Error %d, Severity %d, State %d, ' + @ErrorLineFeed
                           + N'Procedure %s, Line %d, '             + @ErrorLineFeed
                           + N', Message: %s';
  
      SELECT @ErrorStatus    = 1
            ,@ErrorMessage   = ERROR_MESSAGE()
            ,@ErrorNumber    = ERROR_NUMBER()
            ,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
            ,@ErrorLine      = ERROR_LINE()
            ,@ErrorSeverity  = ERROR_SEVERITY()
            ,@ErrorState     = ERROR_STATE()
            ,@RaisErrorState = CASE ERROR_STATE()
                                 WHEN 0 /*RAISERROR Can't generate errors with State = 0*/
                                   THEN 1 
                                 ELSE ERROR_STATE()
                               END;
  
    END CATCH;
  
    /*Rollback to savepoint if error occurred.  This does not affect the transaction count.*/
    IF @ErrorStatus &lt;&gt; 0
      ROLLBACK TRANSACTION @SavepointName;
  
    /*If this procedure executed inside a transaction, then the commit just subtracts one from the transaction count.*/
    COMMIT TRANSACTION;
  
    IF @ErrorStatus = 0
      RETURN 0;
  
    ELSE 
      BEGIN; /*Re-throw error*/
  
        /*Rethrow the error.  The msg_str parameter will contain the original error information*/
        RAISERROR( @MessageTemplate  /*msg_str parameter as message format template*/
                  ,@ErrorSeverity    /*severity parameter*/
                  ,@RaisErrorState   /*state parameter*/
                  ,@ErrorNumber      /*argument: original error number*/
                  ,@ErrorSeverity    /*argument: original error severity*/
                  ,@ErrorState       /*argument: original error state*/
                  ,@ErrorProcedure   /*argument: original error procedure name*/
                  ,@ErrorLine        /*argument: original error line number*/
                  ,@ErrorMessage     /*argument: original error message*/
                  );
  
        RETURN -1;
  
      END;   /*Re-throw error*/
  
  END  /*Stored Procedure*/
  GO
  </code></pre>
  
  <p>He declares the error variables, begins a transaction, sets a save point and then executes the procedure code inside a TRY block.  If the TRY block throws an error , execution passes to the CATCH block, which populates the error variables.  Then execution passes out of the TRY CATCH block.  On error, the transaction rolls back to the save point set at the beginning of the procedure.  Then the transaction commits.  Due to the way SQL Server handles nested transactions, this COMMIT just subtracts one from the transaction counter when executed inside another transaction.  (Nested Transactions really don't exist in SQL Server.)</p>
  
  <p>Sebastian created a very neat and tidy pattern.  Each procedure in an execution chain cleans up its own transactions.  Unfortunately, this pattern has a big problem:  <em>doomed transactions</em>.  Doomed transactions break this pattern because they can't roll back to a save point or commit.  They can only roll back completely.  This, of course, means that you can't set XACT_ABORT ON when using TRY-CATCH blocks (and you should always use TRY-CATCH blocks.)  Even with XACT_ABORT OFF, many errors, such as compilation errors, will doom a transaction anyway.  Further, save points won't work with distributed transactions.</p>
  
  <p>How can I work around this?  I need an error handling pattern that will work within the tSQLt test framework and also deliver consistent, correct error handling in production.  I could check the environment at run-time and adjust the behavior accordingly.  (See the example, below.)  I don't like that, however.  It feels like a hack to me.  It requires that the development environments be configured consistently.  Worse, I don't test my actual production code.  Does anyone have a brilliant solution?</p>
  
  <pre><code>USE TempDB;
  
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  GO
  
  IF OBJECT_ID('dbo.ModifiedRollback') IS NOT NULL
    DROP PROCEDURE dbo.ModifiedRollback;
  GO
  
  CREATE PROCEDURE dbo.ModifiedRollback
  AS
  BEGIN; /*Stored Procedure*/
  
    SET NOCOUNT ON;
  
    IF RIGHT(@@SERVERNAME, 9) = '\LOCALDEV'
      SET XACT_ABORT OFF;
  
    ELSE
      SET XACT_ABORT ON;
  
    BEGIN TRY;
  
      BEGIN TRANSACTION;
  
        RAISERROR('This is just a test.  Had this been an actual error, we would have given you some cryptic gobbledygook.', 16, 1);
  
      COMMIT TRANSACTION;
  
    END TRY
    BEGIN CATCH;
  
      IF @@TRANCOUNT &gt; 0  AND  RIGHT(@@SERVERNAME,9) &lt;&gt; '\LOCALDEV'
        ROLLBACK TRANSACTION;
  
      THROW;
  
    END CATCH;
  
  END;   /*Stored Procedure*/
  GO
  </code></pre>
  
  <p>EDIT:  After further testing, I find that my modified rollback doesn't work, either.  When the procedure throws an error it exits without either rolling back or committing.  tSQLt throws an error because @@TRANCOUNT when the procedure exits doesn't match the count when the procedure starts.  After some trial and error I found a workaround that works in my tests.  It combines the two error handling approaches - making the error handing much more complex, and some code paths can't be tested.  I'd love to find a better solution.</p>
  
  <pre><code>USE TempDB;
  
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  GO
  
  IF OBJECT_ID('dbo.TestedRollback') IS NOT NULL
    DROP PROCEDURE dbo.TestedRollback;
  GO
  
  CREATE PROCEDURE dbo.TestedRollback
  AS
  BEGIN /*Stored Procedure*/
  
    SET NOCOUNT ON;
  
    /* Due to the way tSQLt uses transactions and the way SQL Server handles errors, we declare our error-handling 
     * variables here, populate them inside the CATCH block and then do our error-handling after exiting 
     */
    DECLARE @ErrorStatus       BIT
           ,@ErrorNumber       INT
           ,@MessageTemplate   NVARCHAR(4000)
           ,@ErrorMessage      NVARCHAR(4000)
           ,@ErrorProcedure    NVARCHAR(126)
           ,@ErrorLine         INT
           ,@ErrorSeverity     INT
           ,@ErrorState        INT
           ,@RaisErrorState    INT
           ,@ErrorLineFeed     NCHAR(1) = CHAR(10)
           ,@FALSE             BIT = CAST(0 AS BIT)
           ,@TRUE              BIT = CAST(1 AS BIT)
           ,@tSQLtEnvironment  BIT
           ,@SavepointName     VARCHAR(32) = REPLACE( (CAST(NEWID() AS VARCHAR(36))), '-', '');
           /*Savepoint names are 32 characters long and must be unique.  UNIQUEIDs are 36, four of which are dashes*/
  
    /* The tSQLt Unit Testing Framework we use in our local development environments must maintain open transactions during testing.  So,
     * we don't roll back transactions during testing.  Also, doomed transactions can't stay open, so we SET XACT_ABORT OFF while testing.
     */
    IF RIGHT(@@SERVERNAME, 9) = '\LOCALDEV'
      SET @tSQLtEnvironment = @TRUE
  
    ELSE
      SET @tSQLtEnvironment = @FALSE;
  
  
    IF @tSQLtEnvironment = @TRUE
      SET XACT_ABORT OFF;
  
    ELSE
      SET XACT_ABORT ON;
  
    BEGIN TRY;
  
      SET ROWCOUNT 0; /*The ROWCOUNT setting can be updated outside the procedure and changes its behavior.  This sets it to the default.*/
  
      SET @ErrorStatus = @FALSE;
  
      BEGIN TRANSACTION;
  
        /*We need a save point to roll back to in the tSQLt Environment.*/
        IF @tSQLtEnvironment = @TRUE
          SAVE TRANSACTION @SavepointName;
  
        RAISERROR('Cryptic gobbledygook.', 16, 1);
  
      COMMIT TRANSACTION;
  
      RETURN 0;
  
    END TRY
    BEGIN CATCH;
  
      SET @ErrorStatus = @TRUE;
  
      /* Build a message string with placeholders for the original error information
       * Note:  "%d" &amp; "%s" are placeholders (substitution parameters) which capture
       *        the values from the argument list of the original error message.
       */
      SET @MessageTemplate = N': Error %d, Severity %d, State %d, ' + @ErrorLineFeed
                           + N'Procedure %s, Line %d, '             + @ErrorLineFeed
                           + N', Message: %s';
  
      SELECT @ErrorMessage   = ERROR_MESSAGE()
            ,@ErrorNumber    = ERROR_NUMBER()
            ,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
            ,@ErrorLine      = ERROR_LINE()
            ,@ErrorSeverity  = ERROR_SEVERITY()
            ,@ErrorState     = ERROR_STATE()
            ,@RaisErrorState = CASE ERROR_STATE()
                                 WHEN 0 /*RAISERROR Can't generate errors with State = 0*/
                                   THEN 1 
                                 ELSE ERROR_STATE()
                               END;
    END CATCH;
  
    /* Due to the way the tSQLt test framework uses transactions, we use two different error-handling schemes:
     * one for unit-testing and the other for our main Test/Staging/Production environments.  In those environments
     * we roll back transactions in the CATCH block in the event of an error.  In unit-testing, on the other hand, 
     * we begin a transaction and set a save point.  If an error occurs we roll back to the save point and then 
     * commit the transaction.  Since tSQLt executes all test in a single explicit transaction, starting a 
     * transaction at the beginning of this stored procedure just adds one to @@TRANCOUNT.  Committing the 
     * transaction subtracts one from @@TRANCOUNT.  Rolling back to a save point does not affect @@TRANCOUNT.
     */
    IF @ErrorStatus = @TRUE
      BEGIN; /*Error Handling*/
  
        IF @tSQLtEnvironment = @TRUE
          BEGIN; /*tSQLt Error Handling*/
            ROLLBACK TRANSACTION @SavepointName; /*Rolls back to save point but does not affect @@TRANCOUNT*/
  
            COMMIT TRANSACTION; /*Subtracts one from @@TRANCOUNT*/
          END;   /*tSQLt Error Handling*/
  
        ELSE IF @@TRANCOUNT &gt; 0
          ROLLBACK TRANSACTION;
  
        /*Rethrow the error.  The msg_str parameter will contain the original error information*/
        RAISERROR( @MessageTemplate  /*msg_str parameter as message format template*/
                  ,@ErrorSeverity    /*severity parameter*/
                  ,@RaisErrorState   /*state parameter*/
                  ,@ErrorNumber      /*argument: original error number*/
                  ,@ErrorSeverity    /*argument: original error severity*/
                  ,@ErrorState       /*argument: original error state*/
                  ,@ErrorProcedure   /*argument: original error procedure name*/
                  ,@ErrorLine        /*argument: original error line number*/
                  ,@ErrorMessage     /*argument: original error message*/
                  );
  
      END;   /*Error Handling*/
  
  END  /*Stored Procedure*/
  GO
  </code></pre>