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?
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.