September 24, 2010 at 7:17 am
I created a few store procedures, s1, s2, s3, s4.
In s1, I use a temporary table to store a data like below:
select * into #order from order where city='NY'
select * from #order
Now, the another store procedure, s2,s3,s4, will use the result "select * from #order" to do further process.
I knew that temporary table will destroy after the store procedure close.
Is there another way to handle it?
September 24, 2010 at 7:49 am
You can either use table valued parameter in your stored proc (http://msdn.microsoft.com/en-us/library/bb510489.aspx), use global temporary table (##tableName) or use real table.
September 24, 2010 at 8:15 am
We have to make some assumptions here since you haven't provided all the necessary details.
Here are my assumptions:
The procedures s1, s2, s3, and s4 are called in sequence. Procedure s1 creates the temporary table #orders using select into.
Two options exist for you here.
One, put the calls to s2, s3, and s4 inside s1 as they are dependent on s1.
Two, build a parent sp to wrap around s1, s2, s3, and s4. Explicitly create the temporay table #orders in the parent stored procedure and modify s1 to use the insert into instead of select into. All calls to s1, s2, s3, and s4 would be in the parent procedure.
In both options, the local temporary table #orders will be visible and accessable.
September 24, 2010 at 10:14 am
Thank you for help. I am learning and will try...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply