using the same temp table from multiple stored procs in one master stored proc

Question

_{Thursday, October 25, 2018 2:12 PM}

Using SQL server 2008 R2 Enterprise Edition (Soon to upgrade to the latest SQL version)

I have a master stored proc that runs 5 different stored procs as shown in the sample code. The user runs the master stored proc (EXEC MasterSP) which executes 5 SPs. The user has read, write execute permissions on the database. Please note the temp table being created and loaded by SP1 is being used by the other SP(s) in the master SP. The table created is a local temp table #tmp (not global as ##tmp). The user says that she was able to execute the master SP without any errors earlier, and is now running into errors like unable to connect to #tmp. I changed the local temp table #tmp to global temp table ##tmp and she can now execute the master SP successfully. 

What might have changed in the database settings or permission that was allowing her to run the MasterSP with local temp table earlier, and throwing errors with the local temp table now.

Create proc MasterSP
As
Begin
set nocount ON

EXEC SP1 --Creates and loads a temp table #tmp
EXEC SP2 --updates temp table #tmp
EXEC SP3 --updates temp table #tmp
EXEC SP4 --updates temp table #tmp
EXEC SP5 --writes #tmp to a physical table

End

Thanks in advance for your help.

All replies (1)

_{Thursday, October 25, 2018 6:19 PM ✅Answered}

Without details, it is impossible to know. But the scope and life of temp tables is well-known; there is nothing configurable about that AFAIK. 

When one wants to "share" data among procedures, the standard approach is to create a local temporary table in the outer (MasterSP in your example) and let the inner procedures manipulate and reference as needed. Changing it to a global temp table was not a good idea which you will understand better there are attempts to execute MasterSP simultaneously on different connections. Erland discusses various ways to share data here.