Manipulating the result set of one stored procedure from another....

[Robin Tucker]

Hi,

I have one stored procedure that calls another ( EXEC proc_abcd ). I would
like to return a result set (a temporary table I have created in the
procedure proc_abcd) to the calling procedure for further manipulation. How
can I do this given that TABLE variables cannot be passed into, or returned
from, a stored procedure?

Thanks,

Robin


Example: (if such a thing were possible):


DECLARE @myTempTable1 TABLE ( ID INT NOT NULL )
DECLARE @myTempTable2 TABLE ( ID INT NOT NULL )

.....
/*
Insert a test value into the first temporary table
*/

INSERT INTO @myTempTable1 VALUES ( 1234 )
.....

/*
Execute a stored procedure returning another temporary table of
values.
*/

EXEC proc_abcd @myTempTable2 OUTPUT

....
....

/*
Insert the values from the second temporary table into the first.
*/

SELECT * INTO @myTempTable1 FROM @myTempTable2

[Erland Sommarskog]

Re: Manipulating the result set of one stored procedure from another....

Robin Tucker (idontwanttobes pammedanymore@r eallyidont.com) writes:[color=blue]
> I have one stored procedure that calls another ( EXEC proc_abcd ). I
> would like to return a result set (a temporary table I have created in
> the procedure proc_abcd) to the calling procedure for further
> manipulation. How can I do this given that TABLE variables cannot be
> passed into, or returned from, a stored procedure?[/color]

Have a look at http://www.sommarskog.se/share_data.html where I discuss
various techniques.
[color=blue]
> SELECT * INTO @myTempTable1 FROM @myTempTable2[/color]

You cannot do a SELECT INTO with a table variable.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp