I have a multi-user ASP.NET app running against SQL Server and want to have StoredProcA create a #temptable temp table - not a table variable - to insert some data, then branch to StoredProcB, StoredProcC, and StoredProcD to manipulate the data in #temptable per business rules.
The web app uses connection pooling when talking to SQL. Will I get a new #temptable scratch area for each call of StoredProcA? Or will the connection pooling share the #temptable between users?
To share a temp table between users use two hashes before the name ##like_this.
In this case, though make sure you take steps to avoid clashes with multiple instances of the program.
A ## table will be shared by all users. I assume this is not your intention.
A single-# temp table is visible to all stored procedures down the call stack, but not visible outside that scope. If you can have Proc A call B, C, and D, you should be OK.
Edit: The reporting procedure I should be working on right now is a lot like that. :) I create a temp table (#results) in the root proc that's called by the application, then do some complicated data mangling in a series of child procedures, to 1) abstract repeated code, and 2) keep the root procedure from running to 500+ lines.
#temptable doesn't survive past the end of the procedure in which it was declared, so it won't ever be seen by other users.
Edit: Heh, it turns out that the "nesting visibility" of temp tables has worked since SQL Server 7.0, but I never updated any of my code to take advantage of this. I guess I'm dating myself -- a lot of people probably can't imagine the hell that was SQL Server in the 6.0 and 6.5 days...
Temp tables get created with name mangling under the hood so there shouldn't be conflicts between different stored procedure calls.
If you need to manipulate the same temp data in subsequent stored procedure calls, it's best to just go with a real table and use some sort of unique identifier to make sure you are only dealing with relevant data. If the data is only valuable temporarily, manually delete it once you're done.
Connection pooling (with any modern version of SQL Server) will call sp_reset_connection when reusing a connection. This stored proc, among other things, drops any temporary tables that the connection owns.
From the MS docs:
http://msdn.microsoft.com/en-us/library/ms177399(SQL.90).aspx
Temporary Tables
Temporary tables are similar to permanent tables, except temporary tables are stored in tempdb and are deleted automatically when they are no longer used.
There are two types of temporary tables: local and global. They differ from each other in their names, their visibility, and their availability. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user, and they are deleted when the user disconnects from the instance of SQL Server.
Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created, and they are deleted when all users referencing the table disconnect from the instance of SQL Server.
For example, if you create the table employees, the table can be used by any person who has the security permissions in the database to use it, until the table is deleted. If a database session creates the local temporary table #employees, only the session can work with the table, and it is deleted when the session disconnects. If you create the global temporary table ##employees, any user in the database can work with this table. If no other user works with this table after you create it, the table is deleted when you disconnect. If another user works with the table after you create it, SQL Server deletes it after you disconnect and after all other sessions are no longer actively using it.
Additionally from Curt who corrected the error of my ways and just in case you miss the citation in the comment:
http://msdn.microsoft.com/en-us/library/ms191132.aspx
If you create a local temporary table inside a stored procedure, the temporary table exists only for the purposes of the stored procedure; it disappears when you exit the stored procedure.
If you execute a stored procedure that calls another stored procedure, the called stored procedure can access all objects created by the first stored procedure, including temporary tables.
I find the interplay of the subjects temporary tables/ sessions/ connections/ connection pooling pretty confusing... :( Mark, your statement is among the clearest that I have found doing a lot of Google and MSDN research. Still, I would like to know the source of your statement.
And can someone please explain the difference between session and connection to me? I could not find any hard facts about that.
There seems to be also a problem in terminology, I think: If you "close" a connection, it might be released to the connection pool, so the connection stays (for some time) open... arghs... How can you differentiate between the one flavor of close and the other flavor of close?!
Just tried out the search term sp_reset_connection at Google. This might be helpful: http://www.sqldev.net/misc/sp_reset_connection.htm