CREATE TABLE #ReservedSpaceUsed ( reserved VARCHAR(100), data VARCHAR(100), index_size VARCHAR(100), unused VARCHAR(100) ); INSERT INTO #ReservedSpaceUsed EXEC tSQLt.ResultSetFilter 2, 'EXEC sp_spaceused'; SELECT * FROM #ReservedSpaceUsed;Implications to Testing with tSQLt When you are testing a stored procedure which returns multiple result sets, you will want to use ResultSetFilter to choose an individual result set and store it in a table. The results can then be used with tSQLt.AssertEqualsTable (see Example 3 in the tSQLt Tutorial). Limitations Each time you call ResultSetFilter, the procedure you pass as a command is executed. For example, suppose you want to capture both result sets from sp_spaceused with ResultSetFilter:
CREATE TABLE #DatabaseSize ( database_name nvarchar(128), database_size varchar(18), unallocated_space varchar(18) ); CREATE TABLE #ReservedSpaceUsed ( reserved VARCHAR(18), data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18) ); INSERT INTO #DatabaseSize EXEC tSQLt.ResultSetFilter 1, 'EXEC sp_spaceused'; INSERT INTO #ReservedSpaceUsed EXEC tSQLt.ResultSetFilter 2, 'EXEC sp_spaceused'; SELECT * FROM #DatabaseSize; SELECT * FROM #ReservedSpaceUsed;Since the procedure is executed twice, it is possible that the size of the database has changed between each call and the two results are no longer in sync. This has a greater impact if you are testing a procedure which causes a state change (changing the data or schema of the database) or the data is time-sensitive. Summary tSQLt.ResultSetFilter provide the ability to choose a single result set from a stored procedure which returns multiple result sets. This allows test cases to be written for this type of stored procedure.