Sharing Temporary Table between 2 stored procedures

Question

Sharing Temporary Table between 2 stored procedures

dquirion78

Ten Centuries

Points: 1240
More actions
February 12, 2014 at 7:50 am

#289356

Hi Everyone
ProcA Create the temporary table Table1
ProcA call ProcB
ProcB insert into temporary Table1 some values (insert into #Table1 ... select...)
ProcA select values from temporary table1
I can create and valid both proc but when I try to execute procA
I got this error :
Msg 208, Level 16, State 0, Procedure ProcB, Line 23
Invalid object name '#Table1'.
Seems that I can't use #table1 in procB from ProcA
thanks !!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Should be fine, I've done that before. Can you post the code?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dquirion78 Ten Centuries Points: 1240 More actions · Answer 2

while copying my code I see a typo in declaring #table1 into procb. So it's all fine now

thanks

chris.rogers00 Right there with Babe Points: 790 More actions · Answer 3

Personally, I'd advise against sharing temp tables between nested procedures, it can lead to a world of pain.

Our approach is to have a persisted table, create your records in there and assign each set of records a "RuntimeID" of NEWID(). Then you can pass this value betwen procedures and be confident that you're working on the correct set of data.

The benefits of this are that you can opt to automatically clear down data at the end of execution or persist it for debugging and also there's less change of tempDB getting filled.