Multiple statements inside one BEGIN ... END block

Question

In my installer I have to make a minor change in the schema:

IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[UserProfiles]') AND name = 'AllCheckboxesChecked')
BEGIN
  ALTER TABLE [dbo].[UserProfiles] ADD [AllCheckboxesChecked] [bit] CONSTRAINT [DF_UserProfiles_AllCheckboxesChecked] DEFAULT 0 NOT NULL
  UPDATE [dbo].[UserProfiles] SET [AllCheckboxesChecked]=1 WHERE [CheckedBoxes] LIKE '%#ALL#%'
END
GO

In SSMS this works, but not in Advanced Installer, where it fails with the error message that the column AllCheckboxesChecked does not exist. So I tried:

IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[UserProfiles]') AND name = 'AllCheckboxesChecked')
BEGIN
  ALTER TABLE [dbo].[UserProfiles] ADD [AllCheckboxesChecked] [bit] CONSTRAINT [DF_UserProfiles_AllCheckboxesChecked] DEFAULT 0 NOT NULL
  GO
  UPDATE [dbo].[UserProfiles] SET [AllCheckboxesChecked]=1 WHERE [CheckedBoxes] LIKE '%#ALL#%'
END
GO

but this throws syntax errors as well (not in SSMS, only in AdvInst), so I guess that GO is not allowed inside the BEGIN...END block. The connection is configured as follows:

Connection type: Microsoft SQL Server / MSDE
Connection mode: ODBC Driver
ODBC Driver: SQL Server
Use 64-bit ODBC resource: No

What steps can I take to get the column created and populated with correct values iff the installer runs on a DB where the column does not yet exist?

Answer 1

The column doesn't exist error is due to validation that occurs on existing objects. Since the table already exists, the parser / compiler will verify that the table contains all of the referenced columns.

In order to get around such timing issues with object verification, you can enclose the statement in an EXEC which will not be verified until run-time:

BEGIN
  ALTER TABLE [dbo].[UserProfiles]
    ADD [AllCheckboxesChecked] [bit]
    CONSTRAINT [DF_UserProfiles_AllCheckboxesChecked] DEFAULT 0
    NOT NULL;

  EXEC(N'UPDATE [dbo].[UserProfiles]
         SET [AllCheckboxesChecked]=1
         WHERE [CheckedBoxes] LIKE ''%#ALL#%''');
END;