CODEWITHSUNDEEP
.netsqlsql-server
Marcus
Marcus

Reputation: 8669

SQL Server: Inconsistent results of IF statement

I am doing a data migration and I am currently implementing a reverse-script for our worst case scenario. I might be a little bit tired now but I can´t get my head 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

Running the query above will consistently return a 0 - which is correct, since the FileName column does exist and the FileDataId does not. So far so good.

When I remove the first select statement and uncomment the alter and update code. I get the error:

Invalid column name 'FileDataId'

Query I run after uncommenting 

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:
How come I consistently get the same false result from the IF-statement but when I uncomment the code in the true-clause it suddenly gets entered. Am I completely missing something here?

Upvotes: 1

Views: 140

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239734

SQL Server wants to compile the entire batch before it executes it. It fails to compile it because the column doesn't exist, and so it never gets as far as executing the IF statement.

You have to use dynamic SQL so that it doesn't attempt to compile the statements referencing the column until after you've verified 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

Upvotes: 3

Related Questions