sql - Temporary Table Scope?

Question

I am making use of temporary tables #tempTable in my stored procedure - that I make use to run my ASP.net Reports (Reporting services)

I am doing something like

eg. Code

SELECT * INTO #tempTable FROM Contacts WHERE ContactID < 10

Then I use something like

SELECT o.* FROM #tempTable t INNER JOIN Orders o ON t.ContactID =o.ContactID

to return values to my reports aka results for the stored procedure

I do not get rid of my #tempTable

i.e. I don't do

DROP TABLE #tempTable

I have read that the scope of temporary table is only for the stored procedure - so is doing the above necessary - if I dont do the above what problems will I get into in the future

Answer 1

First, local temporary tables created within a procedure are dropped once the procedure finishes. From the BOL on Create Table:

A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. 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 that called the stored procedure that created the table.

If your data access code is properly opening a connection, calling a stored procedure and then closing the connection, the temp table is created in the procedure is effectively destroyed.

I say "effectively" to bring up another point. I would not recommend dropping the temp table at the end of your procedure although I would add a check just before I created the temp table and drop it if exists (e.g. if object_id('tempdb..#Foo') is not null). The argument against dropping the temp table at the end is that by calling the Drop statement, you are forcing SQL Server to expend resources to destroy the table then and there while you wait for your procedure to end. If instead, you let it go out of scope, your procedure ends immediately and you let SQL Server destroy the table at a time of its own choosing.