BlueRaja - Danny Pflughoeft BlueRaja - Danny Pflughoeft - 3 months ago 27
SQL Question

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

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

I ultimately got it to work by replacing every instance of GO on its own line with

END
GO

---Automatic replacement of GO keyword, need to recheck IF conditional:
IF whatever
BEGIN

This is greatly preferable to wrapping every group of statements in a string, but is still far from ideal. If anyone finds a better solution, post it and I'll accept it instead.

Comments