How to ROLLBACK a transaction when testing using tSQLt

Question

I recently was calling a procedure that contained a rasierror in the code. The raiserror was in a try catch block. Also a BEGIN TRAN was in the same try catch block after the raiserror. The Catch block is designed to ROLLBACK the transaction if the error occurred in the transaction. The way it does this is to check the @@TRANCOUNT if it is greater that 0 I know that it had started a transaction and needs to ROLLBACK. When testing with tSQLt the @@TRANCOUNT is always >0 so if it ever hits the CATCH Block the ROLLBACK is executed and tSQLt fails (because tSQLt is running in a transaction). When I rasie an error and the CATCH block is run tSQLt always fails the test. I have no way to test for the correct handling of the raiserror. How would you create a test case that can potentially ROLLBACK a transaction?

Answer 1

As you mentioned, tSQLt runs every test in its own transaction. To keep track of what is going on is relies on that same transaction to be still open when the test finishes. SQL Server does not support nested transactions, so your procedure rolls back everything, including the status information the framework stored for the current test. At that point tSQLt can only assume that something really bad happened. It therefore marks the test as errored.

SQL Server itself discourages a rollback inside a procedure, by throwing an error if that procedure was called within an open transaction. For ways to deal with this situation and some additional info check out my blog post about how to rollback in procedures.