Nested Stored Procedures

Question

Post reply

Nested Stored Procedures

mishmash2602

Newbie

Points: 4
More actions
April 29, 2009 at 1:15 am

#199965

Hi i have two stored procedures of which the first one calls the other with in it (sproc1 + sproc2)
sproc2 returns a recordset containing two columns of one record long. Is there a way to catch the output of the recordset in sproc1 and place them in output parameters without needing to convert sproc one to return the recordset as output parameters.
My current procedure when executed outputs the recordset but i would like to stop this from happening and place the recordset into output parameters
Thanks
ALTER PROCEDURE sproc1
AS
begin
-- Sproc2 returns recordset
exec sproc2
end

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Vic.K SSC Eights! Points: 970 More actions · Answer 1

In this article you'll find methods hos to share data between procedures:

http://www.sommarskog.se/share_data.html

Kruti Kansara Hall of Fame Points: 3118 More actions · Answer 2

You can use temporary table into SP-1.

in sp-1

create table #temptbl (col1 dt1, col2 dt2)

insert into #temptbl exec sp2

......................

drop table #temptbl

"Don't limit your challenges, challenge your limits"

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

.... and a novel thought...

Why not just have the nested proc return those two values as output parameters itself, rather than as a two-column, one-row result set? Calling procedure catches the output values into it's own output parameters. Problem solved.

However, if you MUST leave the nested procedure as is, you can catch the output values of the two column result set by doing the following. BUT performance will be much slower than just catching output parameters from proc2 and passing them on.

declare @catch table (col1 varchar(50), col2 varchar(50)) --<< use same datatypes as proc2 will return

--

insert into @catch

exec proc2

--

select @outparm1 = col1, @outparm2 = col2 from @catch

--

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Kruti Kansara Hall of Fame Points: 3118 More actions · Answer 4

Bob, i just wanted to say what you said... 🙂

the difference is, i used temporary table and you used table variable.... 😀

"Don't limit your challenges, challenge your limits"

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

Sorry to be redundant, kruti 🙂

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Noffer Ten Centuries Points: 1090 More actions · Answer 6

I agree that output parameter would be the lean way to go.

I've always used temp tables to return a recordset in those cases, must be an old habit as table variables didn't used to work as I remember, but I just tried an and it works, so I guess that I was just stuck in the past.

Kruti Kansara Hall of Fame Points: 3118 More actions · Answer 7

Bob, no need to be sorry!!! 🙂

It happens... 😎

"Don't limit your challenges, challenge your limits"