stored procedure returning 2 tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

stored procedure returning 2 tables

Author

Topic

AAAV
Posting Yak Master

152 Posts

Posted - 2011-12-07 : 09:50:26

I have a stored procedure returning 2 tables and want to insert those rows into tables of its own...

Sample is like
Create procedure proc_a
begin
select * from emp;
select * from dept;
end

I want to be able to capture the result in 2 tables for further processing...

if it returns one table like
insert into #temp
exec proc_b...

is there something like that to capture multiple tables returned from a stored procedure for further processing?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 10:04:09

you cant get both result sets like that.
what you can do is make procedure return only one resultset at a time by means of if else condition using a parameter and then use it in inserts

ie like


Create procedure proc_a
@parameter varchar(30)
begin
if @parameter='value1'
select * from emp;
if @parameter='value2'
select * from dept;
end

then use like 

insert into #temp
exec proc_b 'value1'

insert into #temp1
exec proc_b 'value2'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2011-12-07 : 10:37:34

but the proc always returns 2 result sets in my case

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 10:39:50

quote:
Originally posted by AAAV

but the proc always returns 2 result sets in my case

then you cant put them into same table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2011-12-07 : 11:28:05

i need to put them in different tables if possible

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 11:52:01

quote:
Originally posted by AAAV

i need to put them in different tables if possible

not possible using the same procedure unless you add if condition as shown or putting insert inside procedure code itself.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources