By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,841 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

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

P: n/a

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

Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Robin Tucker (id*************************@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.