Using tSQLt.ResultSetFilter

Overview
The ResultSetFilter procedure provides the ability to retrieve a single result set from a statement which produces multiple result sets. SQL Server comes installed with many stored procedures which return multiple result sets, such as sp_monitor, sp_spaceused and sp_help (when used with certain parameters). It is also common that developers will want to write their own stored procedures which return multiple result sets.

Examples
The most common need to use ResultSetFilter is when you want to capture a specific result set for further processing by another stored procedure. Supposed for example, you wanted to process the data from the second result set of sp_spaceused. This result set contains information on how reserved space in the database is utilized (for data, indexes and unused reserved space).

First we’ll create a table to store the second result set, and then we’ll populate it using the ResultSetFilter. The number 2 that we pass as the first parameter to ResultSetFilter tells us that we want the second result set returned from the stored procedure.

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.