Retrieving result from a stored procedure called within another stored procedure

Retrieving result from a stored procedure called within another stored procedure

Post by Mark Dowel » Sat, 29 Mar 2003 22:28:35



Hello,

I have a stored procedure I created that is calling another stored procedure
within
another application which returns a result set.  So keep in mind I can't
modify the
stored procedure I am calling.

I am trying to access the result set produced by the called stored procedure
with
my stored procedure.

Is there any other way to retrieve these results other than dumping them
into
a temp table?

Thanks

 
 
 

Retrieving result from a stored procedure called within another stored procedure

Post by Vinod » Sat, 29 Mar 2003 22:51:34


Nope ... You have a solution that you donot want to use ... :-) ... Can you tell us why you want to avoid temp tables ???
On the contrary if the SP returns just a single row then you can collect the data as Output parameters from the stored SP.

--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
SCT Software Solutions


> Hello,

> I have a stored procedure I created that is calling another stored procedure
> within
> another application which returns a result set.  So keep in mind I can't
> modify the
> stored procedure I am calling.

> I am trying to access the result set produced by the called stored procedure
> with
> my stored procedure.

> Is there any other way to retrieve these results other than dumping them
> into
> a temp table?

> Thanks


 
 
 

Retrieving result from a stored procedure called within another stored procedure

Post by SriSam » Sat, 29 Mar 2003 23:02:20


Nope, you cannot. You need to use temporary tables. If you had the lattitude
to change the other stored procedure than you have some methods.
--
HTH,
SriSamp
Please reply to the whole group only!


Quote:> Hello,

> I have a stored procedure I created that is calling another stored
procedure
> within
> another application which returns a result set.  So keep in mind I can't
> modify the
> stored procedure I am calling.

> I am trying to access the result set produced by the called stored
procedure
> with
> my stored procedure.

> Is there any other way to retrieve these results other than dumping them
> into
> a temp table?

> Thanks

 
 
 

Retrieving result from a stored procedure called within another stored procedure

Post by Michael MacGrego » Sat, 29 Mar 2003 22:53:34


Quote:>Nope ...

Not true. Check out OPENQUERY. Add a linked server that refers back to the
same server using sp_addlinkedserver or Enterprise Manager, and then use the
OpenQuery as a table in your FROM clause to execute the SP.

Michael MacGregor
Database Architect
SalesDriver

 
 
 

Retrieving result from a stored procedure called within another stored procedure

Post by Doug Gueren » Sat, 29 Mar 2003 23:06:06


You could also open a cursor with your outer stored proc to hold the data
from the inner stored proc although less desirable.


Quote:> Hello,

> I have a stored procedure I created that is calling another stored
procedure
> within
> another application which returns a result set.  So keep in mind I can't
> modify the
> stored procedure I am calling.

> I am trying to access the result set produced by the called stored
procedure
> with
> my stored procedure.

> Is there any other way to retrieve these results other than dumping them
> into
> a temp table?

> Thanks

 
 
 

Retrieving result from a stored procedure called within another stored procedure

Post by Marc Scheune » Sat, 29 Mar 2003 23:17:18




Quote:>I am trying to access the result set produced by the called stored procedure
>with my stored procedure.
>Is there any other way to retrieve these results other than dumping them
>into a temp table?

You could dump it into an in-memory table (instead of writing it back
to disk on the SQL Server).

Marc

================================================================
Marc Scheuner                        May The Source Be With You!
Bern, Switzerland                         m.scheuner(at)inova.ch

 
 
 

Retrieving result from a stored procedure called within another stored procedure

Post by Michael MacGrego » Sat, 29 Mar 2003 23:34:02


Quote:> Nope, you cannot.

Yes you can. See my reply, re: OpenQuery, to Vinod.

MTM

 
 
 

Retrieving result from a stored procedure called within another stored procedure

Post by Michael MacGrego » Sat, 29 Mar 2003 23:34:56


Quote:> You could also open a cursor with your outer stored proc to hold the data
> from the inner stored proc although less desirable.

Yuk! Never use cursors where there is a set-based solution available.
Inserting into a temp table is a good solution but you can also use
OpenQuery, see my reply to Vinod.

MTM

 
 
 

Retrieving result from a stored procedure called within another stored procedure

Post by Michael MacGrego » Sat, 29 Mar 2003 23:37:26


Quote:> You could dump it into an in-memory table (instead of writing it back
> to disk on the SQL Server).

What do you mean by an in-memory table? Do you mean a table variable? If so,
table variables exhibit similar characteristics to temp tables, in that they
do in fact cause disk i/o and use tempdb if I remember correctly. Search
thru the NG for temp tables vs table variables and Hal Berenson who posted
the discussion.

MTM

 
 
 

Retrieving result from a stored procedure called within another stored procedure

Post by Pele » Sun, 30 Mar 2003 03:26:54



>... Search thru the NG for temp tables vs table variables
> and Hal Berenson who posted the discussion.

He doesn't post much and he's not an MVP.

:)

 
 
 

Retrieving result from a stored procedure called within another stored procedure

Post by Michael MacGrego » Sun, 30 Mar 2003 03:38:45


Quote:> He doesn't post much and he's not an MVP.

And your point is ...?

The reference where I first came across this was from Tibor, who is an MVP,
and I have seen other people reference it as well. Unfortunately I don't
have a direct reference for it else I would have provided it.

BTW Do I know you by some other alias or in a past life?

MTM