Search code examples
sql-servertsqlt

How to check if nested stored procedure was executed in tsqlt

I have stored procedure which conditionally execute nested stored procedure.

In unit test, I need to check if this nested stored procedure was executed.

I tried tSQLt.SpyProcedure, but it doesnt seems to work the way i want.

content of my unit test

-- Assembly
exec tSQLt.SpyProcedure 'procedureName', 'raiserror(''procedureName was fired'',16,1)'

-- Assert
exec tSQLt.ExpectException 'procedureName was fired'

-- Action
exec masterProcedureName -- triggers procedureName

but tsqlt.run 'unitestName' returns

failed: (Failure) Expected an error to be raised.

Do you have any idea ?

Solution

  • While, as you mentioned, your approach works, I suggest you use the ..._spyprocedurelog table instead. It’ll allow you to catch multiple executions as well as the parameters passed each time. And if you at some point add error handling to the outer procedure, this will still work.

    Check out the example in the SpyProcedure documentation.