Using a temp table between stored procedures in SQL Server 2008

tsql sql-server-2008 stored-procedures temp-tables

15,713

Solution 1

You need to create the temp table higher up in the calling hierarchy than all the stored procs that consume it. Since you say you cannot alter Main, the only chance to do this would be to create a new outermost procedure that creates #temp and then calls Main.

You can then modify the pre- procedure to populate the already existing table. You shouldn't need to explicitly drop the table anywhere.

Alternatively, you could create a permanent table, and include a spid column that indicates which connection data was inserted from. You'd also ensure that any selects/deletes against this table again use the @@SPID value. I posted this as an answer to Session-global temporary tables

Solution 2

If the main procedure is always run on a single thread and will never be called in parallel from multiple connections, you could declare your temporary table as a global temp table (##temp as opposed to #temp), assuming you are able to change the inner procedure.

This won't work if main is called from several connections at the same time.

15,713

Author by

David Carvalho

My name is Alex. I’m a programmer, and I work at Microsoft. I code, write, speak, listen, ask and learn about how we can make our lives better through the use of technology. I enjoy helping you create new opportunities by leveraging ongoing initiatives. Agility and the will to accomplish something great, coupled with making informed decisions set us up for success. Together, we can work on development, operational and architectural aspects of your Microsoft Azure solutions.

Updated on June 05, 2022

Comments

David Carvalho almost 2 years
I currently have a main stored procedure calling many stored procedures:
```
Main --| 
     --|
       --| > Exec Pre-processing SP (create and populate #temp table)
     --|
       --| > Exec Actual update
     --|
       --| > Exec Post-Process (consume and drop #temp table)
```
The problem I'm currently having is that I need to grab the original values from the database, and the values being passed into the main stored procedure so that I can execute post processing to the update in the last step (stored procedure) being called.

I cannot change the stored procedure signatures or the main stored procedure.

When I try to execute this scenario, I get an invalid object in the post-processing stored procedure.

How can I pass my values from the first stored procedure to the last stored procedure.
David Carvalho almost 13 years

main is called from the UI of the application and can be called in parallel.
Ed Harper almost 13 years

@Alexandre - in which case this solution is no use to you. Sorry.
David Carvalho almost 13 years

I worked around my limitations using the ## global temp table and the @@SPID to get my values from the global temp table.