Store the stored procedure Output in a temp. table

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

SQL Server 2005 Forums

Transact-SQL (2005)

Store the stored procedure Output in a temp. table

Author

Topic

sqlilliterate
Starting Member

40 Posts

Posted - 2008-01-28 : 12:40:35

Hi all,

I've a requirement to store the output of the stored procedure into temp. tables/ table varibles.

I've 4 select statements as my output of the stored procedure. How do I store the results of all the 4 select stmnts into 4 different temp tables.

Simplified SP is as...

Create procedure usp_test
as
begin

select c1,c2 from table1

select c3,4 rom table2

select c9,c8 from table3

select c5,c7 from Table4

end

I'm expecting something like this...

declare @table1 table (c1, c2)
insert into @table1
Exec <Sp_Name>

select * from @table1

I know the above stmnt works, if my SP has only 1 select stmnt as output.

Please help me to acheive this for multiple select statements.

Thanks,
:)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-28 : 12:46:12

If 1st and 2nd columns are of same datatype in each select (ie (c1,c3,c9,c5) & (c2,c4,c8,c7) then join all selects by union all
as

select c1,c2 from table1
union all
select c3,4 rom table2
union all
select c9,c8 from table3
aunion all
select c5,c7 from Table4

then use

INSERT into #table
EXEC SPname params

please note that @tables wont work with INSERT EXEC and you need to use #tables

sqlilliterate
Starting Member

40 Posts

Posted - 2008-01-29 : 00:52:20

The problem is... I cannot modify the SP. I have only the exec access :(
Perhaps, i need to work around with the output data of the SP.

btw,table variables do work with Insert .. Exec
try this in Sql 2005

declare @sp_who table (
spid smallint,
ecid smallint,
status nchar(30),
loginame nchar(128),
hostname nchar(128),
blk char(5),
dbname nchar(128),
cmd nchar(16),
req_id int)

insert into @sp_who execute sp_who
select * from @sp_who

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-29 : 01:35:54

quote:
Originally posted by sqlilliterate

The problem is... I cannot modify the SP. I have only the exec access :(
Perhaps, i need to work around with the output data of the SP.

btw,table variables do work with Insert .. Exec
try this in Sql 2005

declare @sp_who table (
spid smallint,
ecid smallint,
status nchar(30),
loginame nchar(128),
hostname nchar(128),
blk char(5),
dbname nchar(128),
cmd nchar(16),
req_id int)

insert into @sp_who execute sp_who
select * from @sp_who

do you know the data type of results returned by SP?

here you are not doing insert exec.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-29 : 04:20:47

If you want to handle multiple resultsets returned from procedure, in VB, or .NET with the help of ADO, you can pass thru multiple resultsets via recordset object

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources