tsqlt

Is there a way to test other assertions inside a test after calling tSQLt.ExpectException?

Using tSQLt 1.0.5873.27393, I'm trying to write a tSQLt test for a stored procedure that records errors trapped in a CATCH block to a log table before re-throwing the error to the calling session.

I can successfully test that the error message is re-thrown using tSQLt.ExpectException, but a side effect seems to be that it's not possible to test any other assertions after calling tSQLt.ExpectException - so I am unable to test whether the error has been written to the log table.

The simplest test that I can come up with which demonstrates the problem is:

CREATE PROC MyTests.[test ExpectException]
AS
BEGIN
    EXEC tSQLt.ExpectException;
    THROW 50001, 'Error message',1

    EXEC tSQLt.AssertEquals 0,1, '0=1'
END
GO

which produces the following (unexpected) output when executed:

|No|Test Case Name                  |Dur(ms)|Result |
+--+--------------------------------+-------+-------+
|1 |[MyTests].[test ExpectException]|      3|Success|

Using a profiler trace, I can see that the AssertEquals assertion is never executed, because the error is trapped by a CATCH block inside tSQLt.Private_RunTest.

Because tSQLt uses CATCH to trap errors, I suspect that there's no solution to this problem without a major rewrite of tSQLt - but I ask here just in case others have found a way around the issue.

Solution

  • You could follow this approach:

    In the test, wrap your call to the tested stored procedure in a try/catch block. Before the try/catch block, set up an expected variable to 1 and actual to 0. In the test catch block, check if the log table got populated and if yes then flip the actual variable to 1. Write your assertion AFTER the catch block.

    CREATE PROC MyTests.[test ExpectException]
AS
    BEGIN
    DECLARE @expected bit = 1;
    DECLARE @actual bit = 0;

    BEGIN TRY
        -- call tested SP, make sure it fails
        CALL SP..;

        -- add safety net, if the SP call doesn't fail then fail the test
        RAISERROR('Fail the test', 16, 1);
    END TRY
    BEGIN CATCH
        -- pseudo code
        IF EXISTS (SELECT 1 FROM log table)
        -> flip @actual to 1
    END CATCH

    EXEC tSQLt.AssertEquals @expected, @actual
END
GO