fetching a stored procedure

Question

Post reply

fetching a stored procedure

rea|and

Valued Member

Points: 53
More actions
May 20, 2008 at 4:14 am

#190867

Hi guys,
I'd like to fetch stored procedure results (and recordsets) inside another store procedure. I've tried using cursors, but it didn't work.
This is what I want to gain:
save a recordset into a temporary table. So I can query the data freely.
Anyways, once I'll be able to fetch my data, I guess to insert the recordsets into the #table is a minor issue.
Thanks in advance for any suggestions
Andrea

Viewing 10 posts - 1 through 10 (of 10 total)

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

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 1

You'll need a (temporary) table and you can only "catch" the first resultset, and output parameters.

create table #tmpResults (col......)

insert into #tmpResults

exec usp_whatever (.....)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 2

[font="Verdana"]With the help of Output parameter you can fetch the results. In SP_1 declare the parameter as Output and you can get them into SP_2.

Mahesh[/font]

MH-09-AM-8694

rea|and Valued Member Points: 53 More actions · Answer 3

thanks guys,

@ALZDBA: so, you say that there's no way to "catch" a second resultset (my sp returns two recordset).

And actually I've tried to use your method but it seems that a sp that returns more than one recordset fails the "insert into" statement, 'couse, in my case, my recordsets have different number of columns.

anyways, I'll keep on trying.

@mahesh Bote:

What do you mean? Something like

exec my_sp @output_param OUTPUT;

select @output_param;

?

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 4

[font="Verdana"]

@mahesh Bote:
What do you mean? Something like
exec my_sp @output_param OUTPUT;
select @output_param;
?

Yup.:)

Mahesh[/font]

MH-09-AM-8694

rea|and Valued Member Points: 53 More actions · Answer 5

Mahesh Bote (5/20/2008)
[font="Verdana"]
Yup.:)
Mahesh[/font]

mm I've tried in that way too, but probably I don't use the right syntax, because the server returns this error:

Procedure or function sp_01 has too many arguments specified.

(I forgot to tell you that I'm working w/ sql server 2005).

here the code i'm using inside sp_02, the outer sp:

DECLARE @param varchar(120);

--first case: no input param setted

-- returns:

--- Procedure sp_01 has no parameters and arguments were supplied.

--exec sp_01 @output_param OUTPUT;

--second case: one input param

-- returns:

---Procedure or function sp_01 has too many arguments specified.

exec sp_01 '11', @output_param OUTPUT;

select @output_param;

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 6

rea|and (5/20/2008)
thanks guys,
@ALZDBA: so, you say that there's no way to "catch" a second resultset (my sp returns two recordset).
And actually I've tried to use your method but it seems that a sp that returns more than one recordset fails the "insert into" statement, 'couse, in my case, my recordsets have different number of columns.
anyways, I'll keep on trying.

Indeed, if you call a sproc from another sproc, it is only allowed to return a single resultset if you want to capture that data.

@mahesh Bote:
What do you mean? Something like
exec my_sp @output_param OUTPUT;
select @output_param;
?

create proc usp_testSSC

@inputparam integer,

@outputparam varchar(128) OUTPUT

as

begin

if @inputparam = 0

begin

Set @outputparam ='Zero as input parameter'

end

else

begin

Set @outputparam ='nonZero as input parameter'

end

return 0

end

go

declare @outputparam varchar(128)

EXEC usp_testSSC 0, @outputparam OUTPUT

print @outputparam

EXEC usp_testSSC 10, @outputparam OUTPUT

print @outputparam

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

rea|and Valued Member Points: 53 More actions · Answer 7

@ALZDBA, thanks for the code. 🙂

In the end i can't do what I need ... I have to use the second recordset. And I can't switch the recordset returns. mmm

I was thinking about something like

select * (exec my_sp) as res

but obviously it doesn't work.

thanks for your time guys, i'll find some other way.

rea|and Valued Member Points: 53 More actions · Answer 8

Ok, probably I've found a way It could work...but I really don't like it. I'd have to enable the execution of sp from linked servers, I guess. So I'm gonna try tomorrow...

SELECT TMP.*

FROM OPENROWSET('SQLOLEDB', '[server]';'';'[password]',

'EXEC mydb..[my_sp]') AS TMP

-- WHERE some conditions

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 9

[font="Verdana"]DECLARE @param varchar(120);

exec sp_01 '11', @param = @output_param OUTPUT;

select @param

I am not sure about this. Even I can't test it right now as I am sitting somewhere else where SQL is unavailable. Give it try n let me know as well. 😉

Mahesh

[/font]

MH-09-AM-8694