tSQLt - testing code in a CATCH block

• Ash.Shah

  Ten Centuries

  Points: 1042

  More actions

  April 10, 2015 at 7:16 am

  #302930

  Hello,

  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:

  BEGIN TRY

  EXEC InsertLogRecord /* insert the log record */

  INSERT INTO ... /* doing the main purpose of the procedure */

  EXEC UpdateLogRecord @LogID, @status /* update log record with Success */

  END TRY

  BEGIN CATCH

  EXEC UpdateLogRecord @LogID, @status /* update log record with Failure */

  RAISERROR /* throw an error (can't use THROW as in SQL2008R2) */

  END CATCH

  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.

  Many thanks,

  Ash