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

user_238419 0
Viewed 68998

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?

sql-server-2008 tsql sql-server sql
8 Answers

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;
user_62539320

You may try this solution:

if exists(
SELECT...
)
BEGIN
PRINT 'NOT RUN'
RETURN
END

--if upper code not true

ALTER...
GO
UPDATE...
GO
user_119339380

I have used RAISERROR in the past for this

IF NOT whatever BEGIN
    RAISERROR('YOU''RE ALL SET, and sorry for the error!', 20, -1) WITH LOG
END

ALTER TABLE dbo.EMPLOYEE ADD COLUMN EMP_IS_ADMIN BIT NOT NULL
GO
UPDATE dbo.EMPLOYEE SET EMP_IS_ADMIN = whatever
user_7898930
Related