Yesterday I blogged about using WITH RESULT SETS with the EXECUTE command here: Use WITH RESULT SETS to change column names and datatypes of a resultset. Naomi Nosonovsky left the following comment

Can you use this feature to get result sets from the stored procedure returning multiple result sets such as sp_spaceused? If so, can you show this?

So today we are going to look at how we can do this. Let’s say we execute the following stored procedure without specifying an object name

T-SQL
1
EXEC sp_spaceused
EXEC sp_spaceused

You will get two resultsets, the output will look something like this

Now let’s see what happens if we only specify the first result set

T-SQL
1
2
3
4
5
6
7
8
9
10
EXEC sp_spaceused 
WITH RESULT SETS
( 
   (
   DBNAme nvarchar(100),
   DatabaseSize nvarchar(1000),
   UnollactedSpace nvarchar(100)
   )
 
);
EXEC sp_spaceused 
WITH RESULT SETS
( 
   (
   DBNAme nvarchar(100),
   DatabaseSize nvarchar(1000),
   UnollactedSpace nvarchar(100)
   )

);

Here is the output

Msg 11535, Level 16, State 1, Procedure sp_spaceused, Line 128
EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), and the statement tried to send more result sets than this.

As you can see both result sets have to be accounted for when using WITH RESULT SETS
Adding the second result set will fix this, run the following

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
EXEC sp_spaceused 
WITH RESULT SETS
( 
   (
   DatabaseNAme nvarchar(100),
   DatabaseSize nvarchar(1000),
   UnollactedSpace nvarchar(100)
   ),
   (
   Reserved nvarchar(100),
   Data nvarchar(1000),
   IndexSize nvarchar(100),
   Unused nvarchar(100)
   )
);
EXEC sp_spaceused 
WITH RESULT SETS
( 
   (
   DatabaseNAme nvarchar(100),
   DatabaseSize nvarchar(1000),
   UnollactedSpace nvarchar(100)
   ),
   (
   Reserved nvarchar(100),
   Data nvarchar(1000),
   IndexSize nvarchar(100),
   Unused nvarchar(100)
   )
);

Adding the second result set fixed it, it all works now and the column names are the ones we have specified