There was also a ROLLBACK ERROR and tSQLt.ExpectException

Question

Here is the scenario:

1. Stored procedure sproc_a calls sproc_b. Then sproc_b calls sproc_c. A typical nested procedure.
2. Sproc_a did a SET XACT_ABORT ON; and used named transaction.
3. Sproc_c raised an error.
4. tSQLt.ExpectException failed to acknowledge the error. The test should be successful but it failed.

Below is the code to replicate the scenario.

create procedure sproc_c
as
    RAISERROR('An error is found', 11, 1)
go

create procedure sproc_b
as 
    exec dbo.sproc_c;
go

create procedure sproc_a 
as 
SET QUOTED_IDENTIFIER OFF  
SET ANSI_NULLS ON  
SET NOCOUNT ON  
SET XACT_ABORT ON  
SET ANSI_WARNINGS OFF  

    declare @transactionName as varchar(50) = '[POC]';

    begin tran @transactionName
    save tran @transactionName

    exec dbo.sproc_b;
    commit tran @transactionName
go

CREATE PROCEDURE [test sproc_a]
AS
    -- Assert
    BEGIN 
        EXEC tSQLt.ExpectException
            @ExpectedMessage = 'An error is found'
    END 

    -- Act
    BEGIN 
        EXEC dbo.sproc_a
    END 
GO 

EXEC tSQLt.Run '[test sproc_a]'

When I removed the SET XACT_ABORT ON, the unit test is successful but it hitches an error with it: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

This is more like a bug report. Well I guess maybe the question is: anyone who has an idea on how to fix it? :)

Answer 1

Applying the logic from How to ROLLBACK a transaction when testing using tSQLt add a TRY CATCH that checks to see if a ROLLBACK is still needed.

create procedure sproc_c
as
    RAISERROR('An error is found', 11, 1)
go

create procedure sproc_b
as 
    exec dbo.sproc_c;
go

create procedure sproc_a 
as 
SET QUOTED_IDENTIFIER OFF  
SET ANSI_NULLS ON  
SET NOCOUNT ON  
SET XACT_ABORT ON  
SET ANSI_WARNINGS OFF  

    declare @transactionName as varchar(50) = '[POC]';
    BEGIN TRY    
        begin tran @transactionName
        save tran @transactionName

        exec dbo.sproc_b;

        commit tran @transactionName
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
           ROLLBACK;

       -- Do some exception handling

      -- You'll need to reraise the error to prevent exceptions about inconsistent 
      -- @@TRANCOUNT before / after execution of the stored proc.
      RAISERROR('An error is found', 11, 1);
    END CATCH
go

CREATE PROCEDURE [test sproc_a]
AS
    -- Assert
    BEGIN 
        EXEC tSQLt.ExpectException
            @ExpectedMessage = 'An error is found'
    END 

    -- Act
    BEGIN 
        EXEC dbo.sproc_a
    END 
GO 

EXEC tSQLt.Run '[test sproc_a]'

Answer 2

I'm making a separate comment to answer my own question. I've investigated tSQLt.Private_RunTest. It turns out:

1. Private_RunTest has a BEGIN TRAN. Then it will save a named transaction.
2. It is followed by a TRY-CATCH, it will do an exec(@cmd). This basically executes your unit tests. For example: "EXEC tSQLt.Run '[test sproc_a]'".
3. When sproc_a raises an error, Private_RunTest will try to do a ROLLBACK TRAN @TranName. This will fail because it does not rollback the named transaction in sproc_a.

As a resolution, I hacked tSQLt.Private_RunTest and replaced the code "ROLLBACK TRAN @TranName;" with "ROLLBACK TRAN;".

I also added a condition when doing a commit.

I'm not sure what the implications are after doing this change. We'll see how it goes. Below are my changes:

CREATE PROCEDURE tSQLt.Private_RunTest
   @TestName NVARCHAR(MAX),
   @SetUp NVARCHAR(MAX) = NULL
AS
BEGIN
DECLARE @Msg NVARCHAR(MAX); SET @Msg = '';
DECLARE @Msg2 NVARCHAR(MAX); SET @Msg2 = '';
DECLARE @Cmd NVARCHAR(MAX); SET @Cmd = '';
DECLARE @TestClassName NVARCHAR(MAX); SET @TestClassName = '';
DECLARE @TestProcName NVARCHAR(MAX); SET @TestProcName = '';
DECLARE @Result NVARCHAR(MAX); SET @Result = 'Success';
DECLARE @TranName CHAR(32); EXEC tSQLt.GetNewTranName @TranName OUT;
DECLARE @TestResultId INT;
DECLARE @PreExecTrancount INT;

TRUNCATE TABLE tSQLt.CaptureOutputLog;
CREATE TABLE #ExpectException(ExpectException INT,ExpectedMessage NVARCHAR(MAX), ExpectedSeverity INT, ExpectedState INT, ExpectedMessagePattern NVARCHAR(MAX), ExpectedErrorNumber INT, FailMessage NVARCHAR(MAX));

IF EXISTS (SELECT 1 FROM sys.extended_properties WHERE name = N'SetFakeViewOnTrigger')
BEGIN
  RAISERROR('Test system is in an invalid state. SetFakeViewOff must be called if SetFakeViewOn was called. Call SetFakeViewOff after creating all test case procedures.', 16, 10) WITH NOWAIT;
  RETURN -1;
END;

SELECT @Cmd = 'EXEC ' + @TestName;

SELECT @TestClassName = OBJECT_SCHEMA_NAME(OBJECT_ID(@TestName)), --tSQLt.Private_GetCleanSchemaName('', @TestName),
       @TestProcName = tSQLt.Private_GetCleanObjectName(@TestName);

INSERT INTO tSQLt.TestResult(Class, TestCase, TranName, Result) 
    SELECT @TestClassName, @TestProcName, @TranName, 'A severe error happened during test execution. Test did not finish.'
    OPTION(MAXDOP 1);
SELECT @TestResultId = SCOPE_IDENTITY();


BEGIN TRAN;
SAVE TRAN @TranName;

SET @PreExecTrancount = @@TRANCOUNT;

TRUNCATE TABLE tSQLt.TestMessage;

DECLARE @TmpMsg NVARCHAR(MAX);
BEGIN TRY
    IF (@SetUp IS NOT NULL) EXEC @SetUp;
    EXEC (@Cmd);
    IF(EXISTS(SELECT 1 FROM #ExpectException WHERE ExpectException = 1))
    BEGIN
      SET @TmpMsg = COALESCE((SELECT FailMessage FROM #ExpectException)+' ','')+'Expected an error to be raised.';
      EXEC tSQLt.Fail @TmpMsg;
    END
END TRY
BEGIN CATCH
    IF ERROR_MESSAGE() LIKE '%tSQLt.Failure%'
    BEGIN
        SELECT @Msg = Msg FROM tSQLt.TestMessage;
        SET @Result = 'Failure';
    END
    ELSE
    BEGIN
      DECLARE @ErrorInfo NVARCHAR(MAX);
      SELECT @ErrorInfo = 
        COALESCE(ERROR_MESSAGE(), '<ERROR_MESSAGE() is NULL>') + 
        '[' +COALESCE(LTRIM(STR(ERROR_SEVERITY())), '<ERROR_SEVERITY() is NULL>') + ','+COALESCE(LTRIM(STR(ERROR_STATE())), '<ERROR_STATE() is NULL>') + ']' +
        '{' + COALESCE(ERROR_PROCEDURE(), '<ERROR_PROCEDURE() is NULL>') + ',' + COALESCE(CAST(ERROR_LINE() AS NVARCHAR), '<ERROR_LINE() is NULL>') + '}';

      IF(EXISTS(SELECT 1 FROM #ExpectException))
      BEGIN
        DECLARE @ExpectException INT;
        DECLARE @ExpectedMessage NVARCHAR(MAX);
        DECLARE @ExpectedMessagePattern NVARCHAR(MAX);
        DECLARE @ExpectedSeverity INT;
        DECLARE @ExpectedState INT;
        DECLARE @ExpectedErrorNumber INT;
        DECLARE @FailMessage NVARCHAR(MAX);
        SELECT @ExpectException = ExpectException,
               @ExpectedMessage = ExpectedMessage, 
               @ExpectedSeverity = ExpectedSeverity,
               @ExpectedState = ExpectedState,
               @ExpectedMessagePattern = ExpectedMessagePattern,
               @ExpectedErrorNumber = ExpectedErrorNumber,
               @FailMessage = FailMessage
          FROM #ExpectException;

        IF(@ExpectException = 1)
        BEGIN
          SET @Result = 'Success';
          SET @TmpMsg = COALESCE(@FailMessage+' ','')+'Exception did not match expectation!';
          IF(ERROR_MESSAGE() <> @ExpectedMessage)
          BEGIN
            SET @TmpMsg = @TmpMsg +CHAR(13)+CHAR(10)+
                       'Expected Message: <'+@ExpectedMessage+'>'+CHAR(13)+CHAR(10)+
                       'Actual Message  : <'+ERROR_MESSAGE()+'>';
            SET @Result = 'Failure';
          END
          IF(ERROR_MESSAGE() NOT LIKE @ExpectedMessagePattern)
          BEGIN
            SET @TmpMsg = @TmpMsg +CHAR(13)+CHAR(10)+
                       'Expected Message to be like <'+@ExpectedMessagePattern+'>'+CHAR(13)+CHAR(10)+
                       'Actual Message            : <'+ERROR_MESSAGE()+'>';
            SET @Result = 'Failure';
          END
          IF(ERROR_NUMBER() <> @ExpectedErrorNumber)
          BEGIN
            SET @TmpMsg = @TmpMsg +CHAR(13)+CHAR(10)+
                       'Expected Error Number: '+CAST(@ExpectedErrorNumber AS NVARCHAR(MAX))+CHAR(13)+CHAR(10)+
                       'Actual Error Number  : '+CAST(ERROR_NUMBER() AS NVARCHAR(MAX));
            SET @Result = 'Failure';
          END
          IF(ERROR_SEVERITY() <> @ExpectedSeverity)
          BEGIN
            SET @TmpMsg = @TmpMsg +CHAR(13)+CHAR(10)+
                       'Expected Severity: '+CAST(@ExpectedSeverity AS NVARCHAR(MAX))+CHAR(13)+CHAR(10)+
                       'Actual Severity  : '+CAST(ERROR_SEVERITY() AS NVARCHAR(MAX));
            SET @Result = 'Failure';
          END
          IF(ERROR_STATE() <> @ExpectedState)
          BEGIN
            SET @TmpMsg = @TmpMsg +CHAR(13)+CHAR(10)+
                       'Expected State: '+CAST(@ExpectedState AS NVARCHAR(MAX))+CHAR(13)+CHAR(10)+
                       'Actual State  : '+CAST(ERROR_STATE() AS NVARCHAR(MAX));
            SET @Result = 'Failure';
          END
          IF(@Result = 'Failure')
          BEGIN
            SET @Msg = @TmpMsg;
          END
        END 
        ELSE
        BEGIN
            SET @Result = 'Failure';
            SET @Msg = 
              COALESCE(@FailMessage+' ','')+
              'Expected no error to be raised. Instead this error was encountered:'+
              CHAR(13)+CHAR(10)+
              @ErrorInfo;
        END
      END
      ELSE
      BEGIN
        SET @Result = 'Error';
        SET @Msg = @ErrorInfo;
      END  
    END;
END CATCH

BEGIN TRY
    -- Replaced "ROLLBACK TRAN @TranName;" with "ROLLBACK TRAN;". The prior approach can't handle nested named transactions. 
    --ROLLBACK TRAN @TranName;

    ROLLBACK TRAN;
END TRY
BEGIN CATCH
    DECLARE @PostExecTrancount INT;
    SET @PostExecTrancount = @PreExecTrancount - @@TRANCOUNT;
    IF (@@TRANCOUNT > 0) ROLLBACK;
    BEGIN TRAN;
    IF(   @Result <> 'Success'
       OR @PostExecTrancount <> 0
      )
    BEGIN
      SELECT @Msg = COALESCE(@Msg, '<NULL>') + ' (There was also a ROLLBACK ERROR --> ' + COALESCE(ERROR_MESSAGE(), '<ERROR_MESSAGE() is NULL>') + '{' + COALESCE(ERROR_PROCEDURE(), '<ERROR_PROCEDURE() is NULL>') + ',' + COALESCE(CAST(ERROR_LINE() AS NVARCHAR), '<ERROR_LINE() is NULL>') + '})';
      SET @Result = 'Error';
    END
END CATCH    

If(@Result <> 'Success') 
BEGIN
  SET @Msg2 = @TestName + ' failed: (' + @Result + ') ' + @Msg;
  EXEC tSQLt.Private_Print @Message = @Msg2, @Severity = 0;
END

IF EXISTS(SELECT 1 FROM tSQLt.TestResult WHERE Id = @TestResultId)
BEGIN
    UPDATE tSQLt.TestResult SET
        Result = @Result,
        Msg = @Msg
     WHERE Id = @TestResultId;
END
ELSE
BEGIN
    INSERT tSQLt.TestResult(Class, TestCase, TranName, Result, Msg)
    SELECT @TestClassName, 
           @TestProcName,  
           '?', 
           'Error', 
           'TestResult entry is missing; Original outcome: ' + @Result + ', ' + @Msg;
END    

-- Add "IF (@@TRANCOUNT > 0)" so that it will only do the commit if there is a transaction. 
IF (@@TRANCOUNT > 0)
COMMIT;

END;