Testing for Exceptions with tSQLt

I've recently been looking at two relatively new pieces of functionality in tSQLt, the unit testing framework that underpins Red Gate software's SQL Test.

I've written and presented about SQL Test and tSQLt before, including how to upgrade SQL Test to the latest version of tSQLt, but in case you've not been aware of these tools previously, tSQLt is a unit testing framework which allows SQL Server developments to be properly unit tested, within the database, and SQL Test is a user interface which makes it easy to see and run tests.

One of my gripes in the past has been the limited functionality for testing exceptions. Most applications will raise an error in some place or another, hopefully well captured and handled, but not always. Often the database needs to raise an error to feed back to the application, and this should be tested in the same way as any other requirement.
The problem which I have come across in the past is in how to prevent the inbuilt transaction handling capabilities of SQL server from rolling back the test transaction if you hit an error (tSQLt runs all tests in a transaction). This can cause the test to error, which of course is contrary to the desired behaviour. Previously I've had to use a catch-try block to set a variable which I then evaluated to check whether an exception had been raised.

That's where this new functionality comes in - we have two new functions, ExpectException and ExpectNoException which encapsulate the following command, watching for an exception, and pass or fail the test appropriately. ExpectException can even look out for a specific message, and fail if you get a different message.

So, a demonstration. Consider the following stored procedure:

CREATE PROC dbo.MyProc @Myvar INT
AS
IF @Myvar != 1
BEGIN
  RAISERROR ('Variable is not one!',16,1)
END
GO

This will create a procedure that will error if you do not supply a valid input - a number 1. Clearly this is a fabricated example, but it illustrates the point.

This can be tested with the following test:

EXEC tSQLt.NewTestClass @ClassName = N'ExceptionTesting' -- Test class creation
GO


CREATE PROC ExceptionTesting.[test wait for exception old method]
AS
DECLARE @error INT = 0 /* flag to indicate that an error was raised */
BEGIN TRY
  EXEC dbo.MyProc @Myvar = 2 /* the code under test */
END TRY
BEGIN CATCH
  SET @error = 1 /* Set variable as an error was raised */
END CATCH

IF @error != 1  /* Raise an error if the variable has not been set */
  EXEC tSQLt.Fail 'Exception was not raised'
GO

Which will fail the test if an exception is not raised. It is even possible to test for a specific message using ERROR_MESSAGE to determine this in the catch block.

The new functionality however makes this much more user friendly as a test, which of course makes this more maintainable.

So how would this test now look?

CREATE PROC ExceptionTesting.[test wait for exception new method]
AS

EXEC tSQLt.ExpectException @ExpectedMessage  = 'Variable is not one!'
  EXEC dbo.MyProc 2

GO

As you can see, this is much simpler, and easy to see what is being accomplished. It is also possible to check for specific messages (as has been done here), severities and states, as well as using pattern matching (for example for date or time based messages) as part of this same statement.

It is worth noting that this is defined in tSQLt as an expectation rather than an assertion, which may catch some out in terms of naming, but is sensible to denote that the expectation is before the code under test is run, whereas assertions happen afterwards.