SQL Server: inconsistent IF statement results

Question

I am performing data migration, and currently I am using a reverse script for our worst case scenario. I may be a little tired, but I can't get around this thing - so maybe you can help.

I have the following SQL:

IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = N'FileId' AND Object_ID = Object_ID(N'[FileData]')) AND EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FileData' AND COLUMN_NAME = 'FileDataId') BEGIN SELECT 1; --ALTER TABLE [FileData] ADD [FileId] [uniqueidentifier] NULL --ALTER TABLE [FileData] DROP CONSTRAINT PK_FileData --UPDATE [FileData] SET [FileData].[FileId] = [File].[FileId] --FROM [FileData] INNER JOIN [File] on [FileData].FileDataId = [File].FileData --ALTER TABLE [FileData] ALTER COLUMN [FileId] [uniqueidentifier] NOT NULL --ALTER TABLE [FileData] ADD CONSTRAINT PK_FileData PRIMARY KEY (FileId) END ELSE BEGIN SELECT 0; END 

Executing the above query will return 0 sequentially - this is correct, since the FileName column exists, but the FileDataId does not. So far so good.

When I delete the first select statement and uncomment the change and update code. I get an error message:

Invalid column name 'FileDataId'

The request that I run after uncommentation

 IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = N'FileId' AND Object_ID = Object_ID(N'[FileData]')) AND EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FileData' AND COLUMN_NAME = 'FileDataId') BEGIN ALTER TABLE [FileData] ADD [FileId] [uniqueidentifier] NULL ALTER TABLE [FileData] DROP CONSTRAINT PK_FileData UPDATE [FileData] SET [FileData].[FileId] = [File].[FileId] FROM [FileData] INNER JOIN [File] on [FileData].FileDataId = [File].FileData ALTER TABLE [FileData] ALTER COLUMN [FileId] [uniqueidentifier] NOT NULL ALTER TABLE [FileData] ADD CONSTRAINT PK_FileData PRIMARY KEY (FileId) END ELSE BEGIN SELECT 0; END 

My question is:
Why do I consistently get the same false result from the IF statement, but when I uncomment the code in true -clause, it is suddenly entered. Am I missing something here at all?

Answer 1

SQL Server wants to compile the entire batch before it runs it. It cannot compile it because the column does not exist and therefore it never reaches the IF .

You must use dynamic SQL so that it does not try to compile statements referring to a column until you check its existence.

 IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = N'FileId' AND Object_ID = Object_ID(N'[FileData]')) AND EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FileData' AND COLUMN_NAME = 'FileDataId') BEGIN EXEC('ALTER TABLE [FileData] ADD [FileId] [uniqueidentifier] NULL') EXEC('ALTER TABLE [FileData] DROP CONSTRAINT PK_FileData') EXEC('UPDATE [FileData] SET [FileData].[FileId] = [File].[FileId] FROM [FileData] INNER JOIN [File] on [FileData].FileDataId = [File].FileData') EXEC('ALTER TABLE [FileData] ALTER COLUMN [FileId] [uniqueidentifier] NOT NULL') EXEC('ALTER TABLE [FileData] ADD CONSTRAINT PK_FileData PRIMARY KEY (FileId)') END ELSE BEGIN SELECT 0; END