Select Fails With Nonexisitent Columns

Question

Executing the following statement with SQL Server 2005 (My tests are through SSMS) results in success upon first execution and failure upon subsequent executions.

 IF OBJECT_ID('tempdb..#test') IS NULL 
    CREATE TABLE #test ( GoodColumn INT )
 IF 1 = 0 
    SELECT  BadColumn
    FROM    #test

What this means is that something is comparing the columns I am accessing in my select statement against the columns that exist on a table when the script is "compiled". For my purposes this is undesirable functionality. My question is if there is anything that can be done so that this code would execute successfully on every run, or if that is not possible perhaps someone could explain why the demonstrated functionality is desirable. The only solutions I have currently is to wrap the select with EXEC or select *, but I don't like either of those solution.

Thanks

Answer 1

Whether or not this behaviour is "desirable" from a programmer's point of view is debatable of course -- it basically comes down to the difference between statically typed and dynamically typed languages. From a performance point of view, it's desirable because SQL Server needs complete information in order to compile and optimize the execution plan (and also cache execution plans).

In a word, T-SQL is not an interpretted or dynamically typed language, and so you cannot write code like this. Your options are either to use EXEC, or to use another language and embed the SQL queries within it.

Answer 2

If you put:

IF OBJECT_ID('tempdb..#test') IS NOT NULL 
  DROP TABLE #test
GO

At the start, then the problem will go away, as the batch will get parsed before the #test table exists.

What you're asking is for the system to recognise that "1=0" will always evaluate to false. If it were ever true (which could potentially be the case for most real-life conditions), then you'd probably want to know that you were about to run something that would cause failure.

If you drop the temporary table and then create a stored procedure that does the same:

CREATE PROC dbo.test 
AS
BEGIN
  IF OBJECT_ID('tempdb..#test') IS NULL 
    CREATE TABLE #test ( GoodColumn INT )

  IF 1 = 0 
    SELECT  BadColumn
    FROM    #test
END

Then this will happily be created, and you can run it as many times as you like.

Rob

Answer 3

This problem is also visible in these situations:

IF 1 = 1
    select dummy = GETDATE() into #tmp
ELSE 
    select dummy = GETDATE() into #tmp

Although the second statement is never executed the same error occurs. It seems the query engine first level validation ignores all conditional statements.

Answer 4

You say you have problems with subsequent request and that is because the object already exits. It it recommended that you drop your temporary tables as soon as possible when you are done with it.

Read more about temporary table performance at: SQL Server performance.com