#Temp object curiosity
-
-- 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
-
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
K. Brian Kelley
@kbriankelley -
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
-
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply