Why does this update throw an error even though the Alter Table command should be finished?

This has been a nagging issue for me for some time and I would love to know the reason why these SQL Batch commands aren't working.

I have a table that I use to hold configuration settings for a system. When a new setting is added, we add a new field to the table. During an update, I need to change a slew of databases on the server with the same script. Generally, they are all in the same state and I can just do the following:

Alter Table Configuration Add ShowClassesInCheckin bit;
GO

Update Configuration Set ShowClassesInCheckin=ShowFacilitiesInCheckin;
GO

This works fine. However, sometimes one or two databases get updated so I want to write conditional logic to make these changes only if the field doesn't already exist:

if Not Exists(select * from sys.columns where Name = N'ShowClassesInCheckin' AND Object_ID = Object_ID(N'Configuration'))
BEGIN
    Alter Table Configuration Add ShowClassesInCheckin bit;
    Update Configuration Set ShowClassesInCheckin=ShowFacilitiesInCheckin;
END;
GO

In this case, I get an error: "Invalid column name 'ShowClassesInCheckin'" Now, this makes sense in that the Alter Table isn't comitted in the batch before the Update is called (it doesn't work without the "GO" between the Alter and Update). But that doesn't help...I still don't k开发者_如何学Gonow how to I achieve what I am after...

The entire SQL script is parsed before it's executed. During the parsing phase, the column will not exist, so the parser generates an error. The error is raised before the first line of the script is executed.

The solution is dynamic SQL:

exec (N'Update Configuration Set ShowClassesInCheckin=ShowFacilitiesInCheckin;')

This won't get parsed before the exec is reached, and by then, the column will exist.

An alternative that should work is to re-introduce the go. This means that you need to use something else as the condition for the update, possibly based on database name.

if Not Exists(select * from sys.columns where Name = N'ShowClassesInCheckin' AND Object_ID = Object_ID(N'Configuration'))
BEGIN
    Alter Table Configuration Add ShowClassesInCheckin bit;
END;
GO

if *new condition here*
BEGIN
    Update Configuration Set ShowClassesInCheckin=ShowFacilitiesInCheckin;
END;
GO