tSQLt.SpyProcedure [@ProcedureName = ] 'procedure name' [, [@CommandToExecute = ] 'command' ]
Arguments[@ProcedureName = ] ‘procedure name’
The name of an existing stored procedure. @ProcedureName is NVARCHAR(MAX) with no default. @ProcedureName should include the schema name of the stored procedure. For example: MySchema.MyProcedure
[@CommandToExecute = ] ‘command’
An optional command to execute when a call to Procedure Name is made. @CommandToExecute is NVARCHAR(MAX) with no default.
Return Code Values
If the procedure contains more than 1020 parameters, SpyProcedure cannot be used and the following error is raised: Cannot use SpyProcedure on procedure ‘procedure name’ because it contains more than 1020 parameters
If the object given by procedure name does not exist or is not a stored procedure, the follow error is raised: Cannot use SpyProcedure on ‘procedure name’ because the procedure does not exist
Large monolithic stored procedures are difficult to test and maintain. We want to keep our stored procedures small and focused. We also want to test our stored procedures independently of one another. To create independent tests, we can replace the functionality of a stored procedure with a spy. The spy will record the parameters that were passed to it.
SpyProcedure allows tests to be written for a procedure in isolation of the other procedures that it calls. SpyProcedure creates a table with the name of @ProcedureName + ‘_SpyProcedureLog’. This table contains an identity column ‘_id_’ and a column for each procedure parameter (except for cursor output parameters). SpyProcedure also replaces the procedure named by @ProcedureName with the command provided in the @CommandToExecute parameter and a command to insert the parameter values into the SpyProcedureLog table.
Therefore, whenever the @ProcedureName is executed during the test instead of actually running the procedure, a new log entry is made in the @ProcedureName_SpyProcedureLog table and @CommandToExecute is called.
SpyProcedure can not be used with temporary stored procedures (stored procedures whose name begins with #).
SpyProcedure can not be used with procedures which have more than 1020 columns.
Remember that if you are spying a procedure, you are not testing that procedure. Your test is trying to test something else: typically, another procedure’s interaction with the procedure you are spying.
Example: Using SpyProcedure to record parameters passed to a procedure
In this example, we have a sales report which will show either current or historical data based on a parameter. Here we’ll want to test that the SalesReport procedure handles the parameter correctly and calls either HistoricalReport or CurrentReport. We’ll use the assertEqualsTable to make sure the currency parameter is passed correctly to HistoricalReport by looking in the spy’s log.
CREATE PROCEDURE testFinancialApp.[test that SalesReport calls HistoricalReport when @showHistory = 1] AS BEGIN -------Assemble EXEC tSQLt.SpyProcedure 'FinancialApp.HistoricalReport'; EXEC tSQLt.SpyProcedure 'FinancialApp.CurrentReport'; -------Act EXEC FinancialApp.SalesReport 'USD', @showHistory = 1; SELECT currency INTO actual FROM FinancialApp.HistoricalReport_SpyProcedureLog; -------Assert HistoricalReport got called with right parameter SELECT currency INTO expected FROM (SELECT 'USD') ex(currency); EXEC tSQLt.AssertEqualsTable 'actual', 'expected'; -------Assert CurrentReport did not get called IF EXISTS (SELECT 1 FROM FinancialApp.CurrentReport_SpyProcedureLog) EXEC tSQLt.Fail 'SalesReport should not have called CurrentReport when @showHistory = 1'; END; GO
Example: Using SpyProcedure to return a hard-coded set of output parameter values
Suppose we want to test the procedure, IsDiskSpaceTooLow, which returns a 0 if there is enough disk space, and -1 if there is not enough disk space. IsDiskSpaceTooLow conveniently calls another procedure, GetDiskSpace which returns an output parameter for the current disk space. Since setting up a test to fill the drive to a certain size is probably a bad idea, we can test IsDiskSpaceTooLow by using SpyProcedure on GetDiskSpace and hard-coding the output parameter for the purposes of the test.
CREATE PROCEDURE DiskUtil.GetDiskSpace @DiskSpace INT OUT AS BEGIN -- This procedure does something to return the disk space as @DiskSpace output parameter END GO CREATE PROCEDURE DiskUtil.IsDriveSpaceTooLow AS BEGIN DECLARE @DiskSpace INT; EXEC DiskUtil.GetDiskSpace @DiskSpace = @DiskSpace OUT; IF @DiskSpace < 512 RETURN -1; ELSE RETURN 0; END; GO CREATE PROCEDURE testDiskUtil.[test IsDriveSpaceTooLow returns -1 if drive space is less than 512 MB] AS BEGIN EXEC tSQLt.SpyProcedure 'DiskUtil.GetDiskSpace', 'SET @DiskSpace = 511'; DECLARE @ReturnValue INT; EXEC @ReturnValue = DiskUtil.IsDriveSpaceTooLow; EXEC tSQLt.AssertEquals -1, @ReturnValue; END GO