how to avoid tSQLt to abort the called proc after raiserror?

Question

In our stored procedures , we like to "return" a specific return value after raising and error using raiserror()

For that to work, we use low severity levels (eg 12), followed by a return value, such as:

create or alter proc dbo.usp_test 
as
begin
    print '**start of proc'
    raiserror( 'error on purpose',12,1);
    print '**after the error'
    return -3
end

This work perfectly when running that procedure:

declare @RC int
exec @RC = dbo.usp_test
print concat('The return code is ', @RC)

    /* output:

    **start of proc
    Msg 50000, Level 12, State 1, Procedure usp_test, Line 6 [Batch Start Line 12]
    error on purpose
    **after the error
    The return code is -3

    */

However, when that proc is called from a unit test, the behaviour is different, suddenly the execution is stopped after the raiserror:

create schema T_usp_test authorization dbo;
GO
EXECUTE sp_addextendedproperty @name = 'tSQLt.TestClass', @value = 1, @level0type = 'SCHEMA', @level0name = 'T_usp_test'

GO

create or alter proc T_usp_test.[test mytest]
as
begin

    exec tSQLt.ExpectException;

    declare @RC int;
    exec @RC = dbo.usp_test;
    print concat('The return code is ', @RC)


end

GO

exec tSQLt.Run 'T_usp_test.[test mytest]'

    /* output:


    (1 row affected)
    **start of proc

    +----------------------+
    |Test Execution Summary|
    +----------------------+

    |No|Test Case Name            |Dur(ms)|Result |
    +--+--------------------------+-------+-------+
    |1 |[T_usp_test].[test mytest]|      7|Success|
    -----------------------------------------------------------------------------
    Test Case Summary: 1 test case(s) executed, 1 succeeded, 0 failed, 0 errored.
    -----------------------------------------------------------------------------

    */

So the question:

1) why is the behaviour different and is the proc now suddenly stopping executing at raiserror() ?

2) how can I overcome this ?

Answer 1

The core proc that runs the test is tSQLt.Private_RunTest which runs the test in a TRY...CATCH block. From the docs at https://learn.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-2017 :

A TRY...CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection.

It looks like you've set the severity to 12. Consider lowering the severity of your RAISERROR call and see if that changes the behavior. You could also try wrapping your RAISERROR call in its own TRY...CATCH block.