Yesterday I started writing my first tSQLt tests. Mostly with success, except for the following issue.
I have several stored procedures which follow a template where I create a record in a log table, insert some data in another table. If this second operation is successful, I execute a stored procedure to update the log record with a Status of Success. If the operation errors, processing transfers to a CATCH block, where I execute a stored procedure to update the log record with a Status of Failure.
So the procedure looks like this:
EXEC InsertLogRecord /* insert the log record */
INSERT INTO ... /* doing the main purpose of the procedure */
EXEC UpdateLogRecord @LogID, @Status /* update log record with Success */
EXEC UpdateLogRecord @LogID, @Status /* update log record with Failure */
RAISERROR /* throw an error (can't use THROW as in SQL2008R2) */
One of the tests I would like to write is check that the UpdateLogRecord procedure is called with expected parameters when an error occurs i.e. when transferred to the CATCH block.
I understand and have successfully used SpyProcedure. Where I am struggling is how best to cause my procedure to error in my test. The parameters I pass to the stored procedure don't lend themselves to easily raising an error (either varchars or XML).
At the moment I am relying on stubbing my InsertLogRecord to raise an error (using the optional @Command parameter of SpyProcedure). But that is a bit of a hack. I also need that procedure to return a LogID which I then pass to the UpdateLogRecord procedure.
I would appreciate any thoughts the community has and am happy to provide any further details required.