Retrieving multiple Result Sets from within a stored procedure

A coworker of mine came to me with a question about a stored procedure he's working on. In this case, he has a stored procedure which invokes a second stored procedure. He wants that second stored procedure to return two result sets and then make use of both of them in the original stored procedure.

I know how to make a stored procedure return multiple result sets (just do two select statements), but how would you utilize those from the original stored procedure?

Thanks.

You can put the resultset in a temporary table with

INSERT INTO #TempTableName
EXEC SPName

or use a userdefined function rather than a stored procedure if applicable.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||Right - that works great as long as the stored procedure being invoked only returns a single result set. How do you manage if it returns two result sets? That's what I'm most curious about.
|||

You could put the results in unique named global temporary tables. Passing the name to the procedure which then inserts the results and which can be then later used in the outer procedure.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

You could go the global temp route, but I think thats a ridiculous solution to what you have described. Multiple resultsets can be leveraged via stored procs, but usually through ADO (.nextResultset I believe). Dropping these into global temps is just a little overboard for me.
I would recommend just writing the second resultset in its own procedure, or using some variable to indicate which resultset you desire and executing it twice from the parent proc. Or, if theyre identical schemas, just return them with an additional column to indicate the original resultset.

Global temp should be last resort.

Heres an example of what I mean by using an additional column:

--
-- Create / populate 2 tables
--
create table dbo.A (Col1 varchar(10))
go
create table dbo.B (Col1 varchar(10))
go
insert into dbo.A
select 'TableA'
insert into dbo.B
select 'TableB'
go

--
-- Create Child proc that returns 2 resultsets
--
alter procedure [dbo].[p_Child]
as
set nocount on

select *, 'A' from dbo.A
select *, 'B' from dbo.B
go

--
-- Parent proc to call Child
--
alter procedure dbo.p_Parent
as
set nocount on

create table #temp (Col1 varchar(10), Location char(1))

insert into #temp
exec dbo.p_Child

-- return resultsets
select * from #temp where Location = 'A'
select * from #temp where Location = 'B'

drop table #temp
go

--
-- Execute parent to see multiple resultsets returned
--
exec dbo.p_Parent

--
-- cleanup
--
-- drop procedure dbo.p_Child
-- drop procedure dbo.p_Parent
-- drop table dbo.A
-- drop table dbo.B

|||No problem having multiple results sets of the same structure. There will be all the records in temp table as if they combined by union all, and error when recordsets are different.