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?

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; 

GO is not SQL - it is simply a batch separator used in some MS SQL tools.

If you don't use that, you need to ensure the statements are executed separately - either in different batches or by using dynamic SQL for the population (thanks @gbn):

IF whatever
BEGIN
    ALTER TABLE dbo.EMPLOYEE ADD COLUMN EMP_IS_ADMIN BIT NOT NULL;
    
    EXEC ('UPDATE dbo.EMPLOYEE SET EMP_IS_ADMIN = whatever')
END

You could try sp_executesql, splitting the contents between each GO statement into a separate string to be executed, as demonstrated in the example below. Also, there is a @statementNo variable to track which statement is being executed for easy debugging where an exception occurred. The line numbers will be relative to the beginning of the relevant statement number that caused the error.

BEGIN TRAN

DECLARE @statementNo INT
BEGIN TRY
    IF 1=1
	BEGIN
		SET @statementNo = 1
		EXEC sp_executesql
			N'	ALTER TABLE dbo.EMPLOYEE
					ADD COLUMN EMP_IS_ADMIN BIT NOT NULL'

		SET @statementNo = 2
		EXEC sp_executesql
			N'	UPDATE dbo.EMPLOYEE
					SET EMP_IS_ADMIN = 1'

		SET @statementNo = 3
		EXEC sp_executesql
			N'	UPDATE dbo.EMPLOYEE
					SET EMP_IS_ADMIN = 1x'
	END
END TRY
BEGIN CATCH
    PRINT 'Error occurred on line ' + cast(ERROR_LINE() as varchar(10)) 
       + ' of ' + 'statement # ' + cast(@statementNo as varchar(10)) 
       + ': ' + ERROR_MESSAGE()
    -- error occurred, so rollback the transaction
    ROLLBACK
END CATCH
-- if we were successful, we should still have a transaction, so commit it
IF @@TRANCOUNT > 0
    COMMIT

You can also easily execute multi-line statements, as demonstrated in the example above, by simply wrapping them in single quotes ('). Don't forget to escape any single quotes contained inside the string with a double single-quote ('') when generating the scripts.

You can enclose the statements in BEGIN and END instead of the GO inbetween

IF COL_LENGTH('Employees','EMP_IS_ADMIN') IS NULL --Column does not exist
BEGIN
	BEGIN
		ALTER TABLE dbo.Employees ADD EMP_IS_ADMIN BIT
	END

	BEGIN
		UPDATE EMPLOYEES SET EMP_IS_ADMIN = 0
	END
END

(Tested on Northwind database)

Edit: (Probably tested on SQL2012)

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.

You may try this solution:

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

--if upper code not true

ALTER...
GO
UPDATE...
GO

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

You can incorporate a GOTO and LABEL statements to skip over code, thus leaving the GO keywords intact.