How to get SQL Server to ignore checks?

Question

I have a SQL script that populates a temp column and then drops the column at the end of the script. The first time it runs, it works fine because the column exists, then it gets dropped. The script breaks the 2nd time because the column no longer exists, even though the IF statement ensures that it won't run again. How do I get around SQL checking for this field?

IF EXISTS (SELECT name FROM syscolumns 
             WHERE name = 'COLUMN_THAT_NO_LONGER_EXISTS')
BEGIN
   INSERT INTO TABLE1
   (
     COLUMN_THAT_NO_LONGER_EXISTS,
     COLUMN_B,
     COLUMN_C
   )
   SELECT 1,2,3 FROM TABLE2

   ALTER TABLE TABLE1 DROP COLUMN COLUMN_THAT_NO_LONGER_EXISTS
END

Answer 1

I had a similar problem once and got round it by building all the queries as strings and executing them using the Exec() call. That way the queries (selects, inserts or whatever) don't get parsed till they are executed.

It wasn't pretty or elegant though.

e.g

exec('INSERT INTO TABLE1(COLUMN_THAT_NO_LONGER_EXISTS,COLUMN_B,COLUMN_C) SELECT 1,2,3 FROM TABLE2')

Answer 2

Are you checking the column isnt on another table ? If not you probably to check the table too see if statement below.

If you are already doing that is it running a in a single transaction and not picking up the that dropped column has gone ?

IF Not EXISTS (SELECT name FROM sys.columns 
             WHERE name = 'COLUMN_THAT_NO_LONGER_EXISTS' and Object_Name(object_id) = 'Table1')

Created a quick script program for this; can you confirm this matches what you are trying to do because in SQL 2007 at least this isnt returning an error. If i create the table and run through with teh alter table to add colc it works; if i then run the if / insert that works even after dropping the table.

create table tblTests
(
TestID int identity (1,1),
TestColA int null,
TestColB int null
)

go -- Ran this on its own



insert into tblTests (TestColA, TestColB)
Select 1,2
go 10
-- Insert some initial data

alter table tblTests
add TestColC Int
go -- alter the table to add new column

-- Run this with column and then after it has removed it

IF EXISTS (SELECT name FROM sys.columns a 
             WHERE name = 'TestColC' AND
             OBJECT_NAME(object_id) = 'tblTests')
Begin
    insert into tblTests (TestColA, TestColB, testcolc)
    select 1,2,3

    alter table tblTests
    drop column TestColC
End