Someone asked me the question recently about how tSQLt works with TRY..CATCH blocks and the exceptions that we might test for. It works fine, just as it would with other code, but you need to understand that a CATCH still needs to re-throw an exception.

Here’s a short example. I’ve got this query, which has issues.

SELECT TOP 10
         cs.CustomerID
     ,   cs.LastSale
     ,   cs.Salesman
     ,   CAST(cs.SaleValue AS NUMERIC)
     FROM
         dbo.CustomerSales AS cs;

If I run it, I get this:

Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting varbinary to data type numeric.

The CAST here has issues, but that’s fine. Perhaps it’s a data issue, perhaps something else. I can test for that, but for now, I want to be sure I handle these errors correctly.

Now, I embed that in a TRY..CATCH block.

    BEGIN TRY
        SELECT TOP 10
                cs.CustomerID
            ,   cs.LastSale
            ,   cs.Salesman
            ,   CAST(cs.SaleValue AS NUMERIC)
            FROM
                dbo.CustomerSales AS cs;
    END TRY
    BEGIN CATCH
        SELECT @@ERROR
            ,  ‘A CASTing Error has occurred.’
        ;

    END CATCH;

If I do this, and in the CATCH block I "handle" the error, I’m not really error handling. I’m error swallowing. Here are my results.

EXEC spGetCommission 12

I could log this, or try to return some data with a new query, maybe alter something that ensures the client gets results, but what I really need to do is give an error back, but one I’m aware of.

We could delve into error handling, but I won’t do that here. Instead, I want to be sure the application gets an error, when we have an error. It can then decide what the user does or sees.

If I write this test:

ALTER PROCEDURE [misc procs].[test spGetCommission Exceptions]
AS
BEGIN

— Assemble
EXEC tsqlt.ExpectException;

— ACT
EXEC dbo.spGetCommission @userid = 0 — int

— Assert
END;

Now I can run it, but it fails. I see the failure

and I see this in the results

What I should have is something more like this:

    BEGIN CATCH
        THROW 51001,  ‘An CASTING Error has occurred.’, 1;
    END CATCH;

Then my test should be looking for that message.

ALTER PROCEDURE [misc procs].[test spGetCommission Exceptions]
AS
BEGIN

— Assemble
EXEC tsqlt.ExpectException
   @ExpectedMessage = ‘An CASTING Error has occurred.’
   , @ExpectedErrorNumber = 51001
;

— ACT
EXEC dbo.spGetCommission @userid = 0 — int

— Assert

 
END;

If I do that, things work well. The error is handled, but also re-thrown, and my test passes.