December 10, 2003 at 12:45 pm
I need to assign the results from an existing stored procedure (spA) into a temporary table in another stored procedure(spB). How do I do this?
December 10, 2003 at 1:15 pm
Do you want to use one procedure to build a recordset that can then be used in a second procedure? If so, create the temp table before executing the procedures. The temp table will be visible to all subsequently called procedures. However, if a procedure creates a temp table, it will be dropped once the procedure completes.
Brian
create table #temp (col1 int, col2 int,...)
exec insert_proc (populates #temp)
exec manipulate_proc (uses #temp to determine output)
December 10, 2003 at 1:19 pm
you can also use the table datatype to return some data to the calling procedure though the suggestion above is the more straightforward way.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
December 11, 2003 at 6:06 am
Wouldn't the temp table need to be named ##Temp (globalizing it) in order for the the 2nd procedure to see it. #Temp is the localized version.
Matt,
Peace Out!
Regards,
Matt
December 11, 2003 at 6:47 am
As long as the second stored procedure is called from within the first one (the one creating the temp table) then a local temporary table (CREATE TABLE #<TableName>) will work. Nested stored procedures will be able to refer to the temporary table. From Books Online (BOL) under the topic CREATE TABLE:
quote:
A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
December 11, 2003 at 6:59 am
CREATE TABLE #tmp (Id int, ...)
INSERT INTO #tmp
EXEC myStoredProc @param1, ...
Cheers
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply