Select Fail without Columns

Question

Running the next SQL Server 2005 statement (my tests go through SSMS) succeeds on the first run and fails on subsequent launches.

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

This means that something is comparing the columns that I refer to in my select clause to the columns that exist in the table when the script is “compiled”. For my purposes this is undesirable functionality. My question is, is there something that can be done to make this code run successfully every time I run it, or if that is not possible, maybe someone can explain why the desired functionality is desirable. The only solutions that I currently have are to wrap the selection with EXEC or select *, but I don't like any of these solutions.

thanks

Answer 1

If you put:

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

At first, the problem will disappear, since the package will be parsed before the #test table is found.

What you are asking for is a system that recognizes that "1 = 0" will always be evaluated as false. If this were ever (which could potentially be the case for most real-world conditions), then you probably want to know that you are about to run something that might cause a crash.

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 it will be happily created, and you can run it as many times as you want.

Rob

Answer 2

, "" , , , - . , SQL Server ( ).

, T-SQL , . - EXEC SQL-.

Answer 3

:

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

, . , .

Answer 4

You say that you have problems with the subsequent request, because the object is already completed. He recommended that you delete the temporary tables as soon as possible when you are done with it.

For more information on temporary table performance, see: SQL Server performance.com