SQL Server Performance Forum – Threads Archive

temp table session/connection lifespan

Hi There I always thought a temp table lasted the duration of a connection, but i realize this is not the case neccessarily, or rather that it lasts the duration of a session although i am not too clear on the exact definition of that. We have an application that executes 3 stored procedures, one after the other, they all occur in one connection, the connection the the db is only closed right at the end.
Now i want to create a #temp table that can be accessed by the 2nd and 3rd stored procedure, but i see that after the first sp completes the temp table no longer exists. I have thought about using a ##global temp table, but this is created in tempdb not specific to a connection/session, and becuase this is used in a webservice the the sp may be executed multiple times at once, then i will get error about the temp table already existing etc, when a second instance of the sp tries to create the global temp table. What is the best way around this? IS there a way to create a temp table accesible to secondary sp’s not nested within it, but that is also not a global temp table and therefore unique to a session? Thanx

If the table is getting used by the process then why not create this table as a TEMP in user database itself and make the connection to use during the process. As for the temp table I believe it may not be possible to get the results if the connection is closed as it stands for that alone. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Like Satya says, consider creating a fixed table in the database. Otherwise you could use dynamic SQL to create a global temp table with the SPID at the end of the name, and drop this table after the 3rd SP is finished. This does create the problem that all SPs must use dynamic SQL to refer to the table, also the ever-changing table name means you’ll get little or no benefit from execution plan re-use. So a fixed working table would be preferable here.

Thanx guys, that was my next idea, i was just wondering if there was someway of doing it with a #table that i did not know. I will use a fixed table and probably a guid to identify unique rows for each process. Thanx

Hi ya, two options… You can create a script which calls all 3 of your stored procs within one sql call. This is often overlooked but can give performance improvements especially in large batch processing.
e.g. create table #t( col1 int not null )
exec sp1 @par1 = …
exec sp2 @par1 = …
exec sp3 @par1 = … select * from #t execute the whole lot in one hit option 2 involves calling the procs all seperately but creating the temp table outside of any of the proc calls. A temp table created within a proc will only last until that proc terminates, it will not be available to any subsequent calls Cheers
Twan

]]>