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

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 @.myTempTable2Robin Tucker (idontwanttobespammedanymore@.reallyidont.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, esquel@.sommarskog.se

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