tSQLt - How to do a unit test on code in an error Catch block of code

Question

I am trying to implement a UnitTest to test that we log data in a code block that also raises an exception:

So the code looks something like this:

--CREATE TABLE dbo.ErrorLog (ID int IDENTITY (1,1), ErrorDescription Varchar(100))
GO
ALTER PROCEDURE dbo.TestError
AS
DECLARE @Error varchar(100) = 'one is not equal to two'
--do some work
IF 1 <> 2
  BEGIN 
    INSERT dbo.ErrorLog (ErrorDescription) VALUES (@error)
    RAISERROR (@Error,16,1)
    RETURN
  END
GO

And I want to test the Insert of the error into the ErrorLog when the proc I am working on errors. So I wrote my test something like this:

EXEC tSQLt.NewTestClass 'Error'
GO

CREATE PROCEDURE error.test_GivenError_LogResult
AS
--Arrange
EXEC tSQLt.FakeTable 'dbo.ErrorLog'
DECLARE @Actual varchar(100)
DECLARE @Expected Varchar(100) = ('one is not equal to two')

--Act
--EXEC tSQLt.ExpectException @ExpectedMessage = 'one is not equal to two'

EXEC dbo.TestError
SELECT top 1 @Actual = ErrorDescription FROM dbo.ErrorLog

--ASSERT

EXEC tSQLt.AssertEquals @Expected, @Actual 
GO

EXEC tsqlt.Run 'Error'

When I execute this test as per above then it reports an error in the proc and does not do the Assert. If I include the tSQLt.ExpectException it passes on the Exception but will not do the AssertEquals on the ErrorLog result.

Is there a way to work around this?

Answer 1

To do this I would suggest creating a wrapper class around RAISERROR say Utils.GenerateError(@error, @serverity, @state) which in turn calls RAISERROR. That way you can simply use EXEX tSQLt.SpyProcedure 'Utils.GenerateError' to mock out the code that actually raises the error. Note since you are now no longer actually going to throw an error, it would be pointless to call ExpectException in your test. You could check that the correct parameters were passed to the your spy procedure instead. Testing the Utils.GenerateError would be a pretty trivial exercise as well.