sql scope and 'if exists' behavior

Question

I am writing a query that updates schema from an old table structure to a new one. I do not understand why this query:

if exists (
    select * 
    from information_schema.columns 
    where table_name = 'old_table' and column_name = 'old_col')
    begin
    update [dbo].[new_table] 
        set [new_table].[new_col] = [old_table].[old_col] 
        from [dbo].[new_table] 
        inner join [dbo].[old_table] on [new_table].[old_id] = [old_table].[old_id];
    --yada yada yada...
    alter table [dbo].[old_table] drop column [old_col];
    print 'old_table successfully migrated to new_table';
    end
else
    print 'no need to migrate old_table to new_table';

throws this exception:

Msg 207, Level 16, State 1, Line 7, 
Invalid column name 'old_col'.

when the old_col column no longer exits on old_table and I know this because:

select * 
    from information_schema.columns 
    where table_name = 'old_table' and column_name = 'old_col'

doesn't return any rows and I do not see the 'old_col' column in the Object Explorer.

What am I missing here? Why would the query within the conditional statement ever even run?

it's a compilation error, SQL Server have to check schema despite if statements — Roman Pekar, Oct 22 '13 at 14:41
so do I need to do something like `if exists (...) exec sp_executesql('update ... drop column [old_col];')`? — Greg, Oct 22 '13 at 14:45
yes, you can use dynamic SQL and it'll work alright, it has it's own scope — Roman Pekar, Oct 22 '13 at 14:45

sql scope and 'if exists' behavior

0 Answers0