NumbLock (galenathendersonsystemsincdotcom) writes:
> Hello all. I have this perplexing issue with a stored procedure. I'm
> using some conditional statements in creating a local temporary table
> with a select into statement. I dropped the temporary table if it has
> the single row that I was looking for after selecting it. And then after
> dropping the table I try to do another select into a temporary table
> with the same name and I get a compile error.

The story about this error is just hilarous. The error made sense in SQL
6.5, where SQL Server would check queries against temp tables, so if you
misspelled a column name in a temp table, you would get a compilation error.
Under this condition, two definitions of the same temp table would be
one too many.

In SQL 7, Microsoft abandoned this and introduced deferred name resolution.
But this check, that no longer was compelleing, was retained!

Use CREATE TABLE to declare the temp table explicitly. In my opinion,
SELECT INTO should be avoided in production code.

--
Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx