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?

@juergend You took step two before step one. I wrote that I already guessed that. But please answer my first question regarding why it fails with the message that the column AllCheckboxesChecked does not exist. — Alexander, Nov 16 '15 at 16:30

score 7 · Accepted Answer · answered Nov 16 '15 at 16:39

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;

Looks promising; I will check it out ASAP (which means tomorrow morning CET). — Alexander, Nov 16 '15 at 17:33
This solution is correct, I tested it successfully with the latest version of Advanced Installer. (P.S. we build AI) — Bogdan Mitrache, Nov 17 '15 at 08:12

score 0 · Answer 2 · answered Nov 16 '15 at 16:39

The GO statement is a batch terminator and items in a batch only get committed at the end of the batch, either at the next GO statement or when the end of the script is reached. In your case the batch containing the ALTER COLUMN statement has not yet been committed and therefore you get an error that the column does not exist. You will have to split up your scripts in two parts.

score 0 · Answer 3 · edited Nov 16 '15 at 16:40

0

GO is a batch terminator - it's specific to the tool you are using rather than SQL Server - so if you put

Statement1
GO
Statemetn2

That will be sent to SQL Server as two separate executions (batches).

Basically you are breaking your query up into two batches and the first batch you aren't closing the BEGIN block and in the second batch you aren't startinng the END block!

edited Nov 16 '15 at 16:40

Solomon Rutzky

41,664
6
112
149

answered Nov 16 '15 at 16:39

Tony Rogerson

579
2
6

Multiple statements inside one BEGIN ... END block

3 Answers3