#Temp Tables

[Per]

Why cant I use the same temptable name i a stored procedure after i have droped it?

I use the Pubs database for the test case.

CREATE PROCEDURE spFulltUttrekk AS

SELECT *
INTO #temp
FROM Jobs

SELECT *
FROM #temp

DROP TABLE #temp

SELECT *
INTO #temp
FROM Employee

SELECT *
FROM #temp

[Erland Sommarskog]

[posted and mailed, vänligen svara i nys]

Per (pe************************@cgey.com) writes:

Why cant I use the same temptable name i a stored procedure after i have
droped it?

I use the Pubs database for the test case.

CREATE PROCEDURE spFulltUttrekk AS

SELECT *
INTO #temp
FROM Jobs

SELECT *
FROM #temp

DROP TABLE #temp

SELECT *
INTO #temp
FROM Employee

SELECT *
FROM #temp

When SQL Server builds the query plan for a procedure, it builds the
plan for the entire procedure in one go, with one exception. If a
statement refers to a non-existing table, that statement is deferred
until run-time.

So when you create the procedure, SQL Server defers the plan for the
two SELECT statements. When execution hits the deferred statement, SQL
Server recompiles the procedure. And the entire procedure. So when it
finds a SELECT * INTO #temp, it thinks that's bad, because #temp does
already exist. At this point, the DROP TABLE statement has not been
executed, so SQL Server does not know that the table will go away.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp