CODEWITHSUNDEEP
sqlsql-server-2005
AD.Net
AD.Net

Reputation: 13399

IF EXISTS not working

I am trying to create a re-runnable script

IF EXISTS ((SELECT * from information_schema.columns where TABLE_SCHEMA=N'MySchema' and TABLE_NAME=N'Table1' AND COLUMN_NAME=N'Column1'))
BEGIN
   PRINT 'Updating'

   UPDATE [Table2] 
   SET Column = 0
   WHERE Id IN (SELECT t2.Id 
                FROM [Table2] t2 
                JOIN [MySchema].[Table1] t1 ON t2.UserId = t1.UserId 
                WHERE t1.Column1 = 0)

   ALTER TABLE [MySchema].[Table1] DROP COLUMN Column1
END

It works the first time, but 2nd time I get the error

Invalid column name 'Column1'.

Any help would be appreciated. I was using sys.columns before to check if the column exists. It's for SQL Server 2005.

Upvotes: 2

Views: 1829

Answers (2)

Andriy M
Andriy M

Reputation: 77667

If you are fine with splitting your script into batches, you could use the SET NOEXEC command.

Set it ON when the column doesn't exist. Put the UPDATE and ALTER TABLE into a separate batch, i.e. separate it from the condition with a GO. Put another GO and add SET NOEXEC OFF if there are other statements in your script that need to be executed, which do not depend on the condition.

For an illustration, this is what I mean:

IF NOT EXISTS (
  SELECT *
  from information_schema.columns
  where TABLE_SCHEMA=N'MySchema'
    and TABLE_NAME=N'Table1'
    AND COLUMN_NAME=N'Column1'
)
  SET NOEXEC ON
GO

PRINT 'Updating'

UPDATE [Table2] 
SET Column = 0
WHERE Id IN (SELECT t2.Id 
             FROM [Table2] t2 
             JOIN [MySchema].[Table1] t1 ON t2.UserId = t1.UserId 
             WHERE t1.Column1 = 0)

ALTER TABLE [MySchema].[Table1] DROP COLUMN Column1
GO
SET NOEXEC OFF
... -- the rest of the script, if any

Upvotes: 1

devio
devio

Reputation: 37215

T-SQL is not a batch script, and the parser checks the whole statement before executing it. Therefore, if the column in the sub-select does not exist, the check will fail before execution even starts.

For your code to work, you need to pass the UPDATE and ALTER statements as string to EXECUTE or EXEC sp_executesql.

See also Q2586737

Upvotes: 2

Related Questions