(SQL) How would I return the result set from multiple executed stored procedures?

Question

Okay I have a queswtion about returning the recordset from multiple stored procedures.

If I have a sproc which does this:

exec sproc1
exec sproc2
exec sproc3

Each sproc returns just one number, so it return

[no column name] 1500

[no column name] 18000

[no column name] 1253

Obviously a datareader won't be able to handle this, or at least I don't think it will. So I thought about declaring a Table variable

DECLARE @Table (Col1, Col2)

INSERT INTO @Table
exec sproc1,exec sproc2, etc...)

But that doesn't work.

Any ideas on how to handle this?

Answer 1

Spread your INSERTs over several statements:

DECLARE @t TABLE (Value int);

INSERT INTO @t
EXEC sproc1;

INSERT INTO @t
EXEC sproc2;

INSERT INTO @t
EXEC sproc3;

SELECT Value FROM @t;

Edit

Wait ... are you saying that you already have a stored procedure which returns multiple resultsets? DataReaders can definitely handle that. Just use the NextResult() method to tell the reader that you now want data from the next resultset.

Edit 2

You asked if there's a way to roll all the results into one row. I can think of one way to do this but it's definitely not pretty, and I'm sure I'll be chastised by more SQL-savvy users in the comments, but here goes:

DECLARE @t TABLE (Id int identity, Value int);

INSERT INTO @t (Value)
EXEC sproc1;

INSERT INTO @t (Value)
EXEC sproc2;

INSERT INTO @t (Value)
EXEC sproc3;

SELECT Value FROM @t
PIVOT ( AVG(Value) FOR Id IN ([1], [2], [3]) ) AS t;

So that inserts the three values as separate rows, and then pivots on the Id values to create a single row with three columns.