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

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

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

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