TSQL - How to use GO inside of a BEGIN .. END block?

Question

I am generating a script for automatically migrating changes from multiple development databases to staging/production. Basically, it takes a bunch of change-scripts, and merges them into a single script, wrapping each script in a IF whatever BEGIN ... END statement.

However, some of the scripts require a GO statement so that, for instance, the SQL parser knows about a new column after it's created.

ALTER TABLE dbo.EMPLOYEE  ADD COLUMN EMP_IS_ADMIN BIT NOT NULL GO -- Necessary, or next line will generate "Unknown column:  EMP_IS_ADMIN" UPDATE dbo.EMPLOYEE SET EMP_IS_ADMIN = whatever 

However, once I wrap that in an IF block:

IF whatever BEGIN     ALTER TABLE dbo.EMPLOYEE ADD COLUMN EMP_IS_ADMIN BIT NOT NULL     GO     UPDATE dbo.EMPLOYEE SET EMP_IS_ADMIN = whatever END 

It fails because I am sending a BEGIN with no matching END. However, if I remove the GO it complains again about an unknown column.

Is there any way to create and update the same column within a single IF block?

Answer 1

I had the same problem and finally managed to solve it using SET NOEXEC.

IF not whatever BEGIN     SET NOEXEC ON;  END  ALTER TABLE dbo.EMPLOYEE ADD COLUMN EMP_IS_ADMIN BIT NOT NULL GO UPDATE dbo.EMPLOYEE SET EMP_IS_ADMIN = whatever  SET NOEXEC OFF;