CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
BlueRaja - Danny Pflughoeft
BlueRaja - Danny Pflughoeft

Reputation: 85986

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?

Upvotes: 120

Views: 78380

Answers (8)

Luk
Luk

Reputation: 71

You may try this solution:

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

--if upper code not true

ALTER...
GO
UPDATE...
GO

Upvotes: 2

kavun
kavun

Reputation: 3381

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

Upvotes: 0

Mina Jacob
Mina Jacob

Reputation: 1951

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;

Upvotes: 72

Andy Joiner
Andy Joiner

Reputation: 6541

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)

Upvotes: 13

jim a
jim a

Reputation: 15

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

Upvotes: -1

mellamokb
mellamokb

Reputation: 56769

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.

Upvotes: 18

BlueRaja - Danny Pflughoeft
BlueRaja - Danny Pflughoeft

Reputation: 85986

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.

Upvotes: 9

Oded
Oded

Reputation: 498982

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

Upvotes: 55

Related Questions