Search code examples
sql-servert-sqlstored-procedurestemp-tablessql-server-2014-express

How to create a table that exist temporarily and that is accessible from many stored procedures?

I query my database from within a .NET application (time recoding). I send the Windows user name to the database and query it depending on this information.

I query a lot of different information with stored procedures, such as holiday, overtime etc. and send them back to the .NET application where I show this data in one form.

Let's say I need the information from SP1, 3 and 4 on Form1:

FORM1:

SP1
SP3
SP4

My problem:

I have a quite complicated table that I need for these stored procedures. At the moment I create this table in every of those stored procedures as temporary tables what is obviously quite time consuming.

What I have tried so far is creating a stored procedure that creates a temp table but this one is not accessable within my other stored procedures.

My question: I am looking for a way to create this table for Form1 once, so I can just access these table in the other stored procedures.

I'm using SQL Server 2014 Express.

Solution

  • If you create the temporary table outside of any stored procedure, it will be accessible within each. Assuming that the actual table definition isn't too complex (but populating it may be), this may be doable.

    So you'd execute (effectively):

    CREATE TABLE #T (A int not null, B varchar(17) not null)
EXEC PopulateTempTable
EXEC SP1
EXEC SP3
EXEC SP4

    All on a single connection. PopulateTempTable may or may not be required, depending on how complex the population of the table is and whether you want that to be performed by your calling code or the database.

    You cannot create the temp table inside of a stored procedure since temp tables are automatically dropped when the scope of a stored procedure is exited.

    Alternatively, you may want to simulate "session-global" temp tables, as I suggest in this answer1.

    The risk with using a global temp table is that it is truly global - all sessions see and interact with the data in the same table. You have to be very careful in such circumstances if it's at all possible that two sessions will attempt to use it at the same time; You generally need some means to filter the data in the table so that each session only works with its own data.

    1Probably enhanced these days by also adding a Logon Trigger to clear down the old contents of the table whenever a new connection is established.