#Temp object curiosity

Question

#Temp object curiosity

ThomasH

SSCertifiable

Points: 7437
More actions
December 19, 2003 at 11:56 am

#106666

-- Why does this work
Declare @sql varchar(8000)
Set @sql = 'Create Procedure #MyTempSP as Select GetDate()'
Exec (@SQL)
Exec #MyTempSP
Drop Procedure #MyTempSP
-- and this does not
Declare @SQL2 varchar(8000)
Set @SQL2 = 'Create Table #MyTempTB (Col1 Int)'
Exec (@SQL2)
Select * from #MyTempTB
Drop Table #MyTempSP

Once you understand the BITs, all the pieces come together

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

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

Temporary stored procedures with # are local and are visible only to the connection. Therefore, when you create the temp sproc, the connection can access it, even though you're using dynamic SQL, which creates the sproc in a totally different batch.

Temporary tables using # are local in scope, but that's not to the connection, but the calling batch. That means the stored procedure and any stored procedures called within the same context can reference the # temp table, but other batches cannot. Since the dynamic SQL creates the table in a separate batch altogether, the original stored procedure cannot see the temp table. In fact, it's destroyed when the dynamic SQL batch is completed (which is after the table is created since that's the only statement in the batch).

K. Brian Kelley, GSEC

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/

K. Brian Kelley
@kbriankelley

SJTerrill SSCrazy Points: 2938 More actions · Answer 2

From BOL CREATE TABLE:

quote:
A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.

So, even though the sproc persists for the session via the Exec() shell, the same can't be said of the table.

Do you have a specific need to do something like this?

SJTerrill

ThomasH SSCertifiable Points: 7437 More actions · Answer 3

No specific reason, but I was looking at some old TSQL I wrote a while back, and asked myself "How does the #Proc persist?".

Thanks for the answers. It is curios though.... but I guess because the CREATE PROC statetment must be the only statement in a batch, it seems approprite that the existance of the proc lasts longer than the batch itself.:)

Once you understand the BITs, all the pieces come together