I have stored procedures that need to return a resultset. Because of they they work they need to do several selects to compose those resultsets. So I insert into the table as needed, and at the end I select from it, and then drop it before exiting the procedure.
My question is several parts.
1. If I create a temp table called #Results inside a stored procedure, is the stored procedure it was created in reentrant?
2. If I create a temp table called #Results inside a stored procedure, and that procedure gets called concurrently by two different queries, how many temp tables were created to fulfill those queries - in total - one or two?
3. If I create a temp table called #Results inside a stored procedure, can stored procedures it calls gain access to that temporary table or do I need to create ##Results temp table to do that?
What I have tried:
Currently I am simply creating and dropping #Results within the scope of one routine, but I have concerns about reentrancy and otherwise concurrency .
Submit an article or tip