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]

Robin Tucker (id************ *************@r eallyidont.com) writes:

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?
Have a look at http://www.sommarskog.se/share_data.html where I discuss
various techniques.
SELECT * INTO @myTempTable1 FROM @myTempTable2

You cannot do a SELECT INTO with a table variable.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp