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?

See http://stackoverflow.com/questions/4855537/sql-server-2000-alter-table-insert-into-errors/4855582#4855582 please — gbn, Jun 16 '11 at 19:01
@gbn: Yes, I realize why this happens *(see second paragraph)*; but I have no idea how to work around it - do I *really* need to turn every query into a bunch of strings!? — BlueRaja - Danny Pflughoeft, Jun 16 '11 at 20:25
@BlueRaja: What's the concern? If it works, that's all that matters at the end of the day. If there's a legitimate business problem with the solution provided, please express that. Is there something specifically disconcerting about converting every query into a bunch of strings? — mellamokb, Jun 16 '11 at 20:32
@mellamokb: Yes, there's a problem; if the word GO is used in any other context (such as a comment, or a string), the script won't work. Also, we lose the useful line-numbers in error messages in case anything goes wrong. Is there no way to do this with transactions? Or try/catch? — BlueRaja - Danny Pflughoeft, Jun 16 '11 at 20:57
@BlueRaja: 1) I believe `GO` has to be on a line by itself, so you can search for that case only, and not every instance of the word `GO`. 2) You can always log which statements were completed successfully. Or you could wrap the whole thing in a try/catch and use your own line numbers using some variable, like @lineNo, that you keep track of, and report out on error. Since you are generating these automatically, making changes like this should be a breeze. It just sounds like you plain don't want to explore this route when I think there are solutions to be found for all of your concerns. — mellamokb, Jun 17 '11 at 01:00
@BlueRaja: See my updated answer to get an example of what I mean. You can run the script and it will error on the third statement, which should show in the error output that it was line 2 of statement 3. You could just as easily log the output to a table or whatever works best in your script automation. Please let me know if this helps at all, I want to help you find a solution that works for you :-) — mellamokb, Jun 17 '11 at 01:08
@BlueRaja: Even with a try/catch or transaction (which I would definitely recommend anyway, see how I have incorporated both into my answer), the problem you will always have is that the entire script is parsed, compiled and validated before it is ever run, so it will always complain about the missing column instead of running the script. You either have to run the script as strings or run the scripts separately and individually. — mellamokb, Jun 17 '11 at 01:14
When updating an existing table with a new non-nullable column, this is the canonical way to accomplish that. Test whether the column already exists, if not, create the new column as nullable, set a default value for all rows, ALTER the new column definition to NOT NULL. It is simply inexcusable that such a standard task should be so difficult. — David, Mar 09 '20 at 18:35

score 67 · Accepted Answer · edited May 08 '19 at 14:58

67

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;

edited May 08 '19 at 14:58

Ben Gripka

16,012
6
45
41

answered Oct 20 '16 at 01:37

Mina Jacob

1,881
22
23

3

This is a great solution! – Bazinga Jun 14 '17 at 18:18
2

+1! This is the ONLY *practical* Answer so far for use in a SS `SQLCMD` Mode Script (i.e. a master deployment script) that calls (via `:r` command) other SS Scripts (i.e. sub-deployment scripts) with some of those calls inside `if` Statements. Oded's, mellamokb's and Andy Joiner's Answers of enclosing *all* those Statements in `exec` Calls / `begin`-`end`'s are non-starters. Also, the `begin`-`end` method won't work if there's a `create` Statement (e.g. requires an explicit `go` prior to it). But, man, "Holy double negatives, Batman!" ;) – Tom Jun 23 '17 at 03:12
2

For readability (e.g., to help overcome the double-negatives and make it clearer that it's simulating a *virtual* `if`-block), I would prefix the block with an `-- If whatever` Comment, indent the block and postfix the block with an `--end If whatever` Comment. – Tom Jun 23 '17 at 03:23
1

You saved my bacon! I was running merge statements and those dumb GO's don't like to be inside a IF BEGIN END ELSE – Omzig Nov 21 '18 at 19:57
Hm, I'm getting an error on the update somehow after set noexec on has been executed? (error that the column name to update is invalid) Running on MSSQL 2014 in the query editor. Works fine if the condition turns false (thus noexec remains off) – Jerry Sep 30 '19 at 12:58
For incrementive scripts, appropriate solution is [this one](https://stackoverflow.com/a/6376909/3127815). Consecutive runs of the script above would cast `Column names in each table must be unique. Column name 'EMP_IS_ADMIN' in table 'EMPLOYEE' is specified more than once.` – Turbo Jul 06 '23 at 08:25

Oded · Answer 2 · 2011-06-16T19:03:30.573

54

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

edited Jun 16 '11 at 19:03

answered Jun 16 '11 at 18:41

Oded

489,969
99
883
1,009

9

Yes, I understand that. This does not answer the question - I need to create and update a column in the same `IF` block. – BlueRaja - Danny Pflughoeft Jun 16 '11 at 18:43
@Oded: Would the `;` help out here? - You've just edited your answer :o) – Neil Knight Jun 16 '11 at 18:45
@Neil - this is my thinking, yes. – Oded Jun 16 '11 at 18:46
`;` doesn't work either - the parser is still giving me *"Invalid column name 'EMP_IS_ADMIN'."* – BlueRaja - Danny Pflughoeft Jun 16 '11 at 18:51
When the batch is compiled, EMP_IS_ADMIN does not exist. http://stackoverflow.com/questions/4855537/sql-server-2000-alter-table-insert-into-errors/4855582#4855582 – gbn Jun 16 '11 at 19:01
Mina Jacob's `set noexec` Answer is the ONLY *practical* Answer so far for use in a SS `SQLCMD` Mode Script (i.e. a master deployment script) that calls (via `:r` command) other SS Scripts (i.e. sub-deployment scripts) with some of those calls inside `if` Statements. Oded's, mellamokb's and Andy Joiner's Answers of enclosing *all* those Statements in `exec` Calls / `begin`-`end`'s are non-starters. Also, the `begin`-`end` method won't work if there's a `create` Statement (e.g. requires an explicit `go` prior to it). – Tom Jun 23 '17 at 03:15

score 18 · Answer 3 · edited Jun 13 '12 at 13:35

18

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.

edited Jun 13 '12 at 13:35

SteveC

15,808
23
102
173

answered Jun 16 '11 at 18:41

mellamokb

56,094
12
110
136

Don't think this would work for commands split across multiple lines, would it? – BlueRaja - Danny Pflughoeft Jun 16 '11 at 18:54
@BlueRaja: I have updated the example to show how it would work. Those strings can be multi-line, as long as any single quotes (') contained inside are escaped by using a double-single quote ('') – mellamokb Jun 16 '11 at 19:03
1

@mellamokb: strictly speaking, only the UPDATE needs sp_executesql ... http://stackoverflow.com/questions/4855537/sql-server-2000-alter-table-insert-into-errors/4855582#4855582 – gbn Jun 16 '11 at 19:06
1

@gbn: True. But if you're going to automate this for 100's of statements, it will be easier to just apply it blindly across all statements instead of deciding when and where you need it. – mellamokb Jun 16 '11 at 19:16
@gbn @mellamokb: I meant statements like `SELECT * FROM whatever`. If I execute every line with its own EXEC statement, that will break. Or are you suggesting I break at every `GO` statement? – BlueRaja - Danny Pflughoeft Jun 16 '11 at 20:18
@BlueRaja: Correct, that is the whole point. I guess I didn't explain that, but I assumed you understood that I am putting each batch within a string, i.e., split around each `GO`. – mellamokb Jun 16 '11 at 20:34
Mina Jacob's `set noexec` Answer is the ONLY *practical* Answer so far for use in a SS `SQLCMD` Mode Script (i.e. a master deployment script) that calls (via `:r` command) other SS Scripts (i.e. sub-deployment scripts) with some of those calls inside `if` Statements. Oded's, mellamokb's and Andy Joiner's Answers of enclosing *all* those Statements in `exec` Calls / `begin`-`end`'s are non-starters. Also, the `begin`-`end` method won't work if there's a `create` Statement (e.g. requires an explicit `go` prior to it). – Tom Jun 23 '17 at 03:15

Andy Joiner · Answer 4 · 2016-08-24T08:56:57.837

13

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)

edited Aug 24 '16 at 08:56

answered Oct 16 '13 at 08:04

Andy Joiner

5,932
3
45
72

1

Please give reason for -1 – Andy Joiner Apr 16 '14 at 10:10
1

Don't know why it was downvoted... works like a charm for me. – Thorarin Feb 12 '15 at 14:55
12

Using SQL Server 2008 R2, this doesn't seem to work for me, I still get an error 'Invalid column name 'EMP_IS_ADMIN'.' on the UPDATE line. – MerickOWA May 07 '15 at 19:45
The BEGIN-END batching worked for me using SQL Server 2016. IMO this is the cleanest syntax. – Uber Schnoz Mar 27 '17 at 19:57
Mina Jacob's `set noexec` Answer is the ONLY *practical* Answer so far for use in a SS `SQLCMD` Mode Script (i.e. a master deployment script) that calls (via `:r` command) other SS Scripts (i.e. sub-deployment scripts) with some of those calls inside `if` Statements. Oded's, mellamokb's and Andy Joiner's Answers of enclosing *all* those Statements in `exec` Calls / `begin`-`end`'s are non-starters. Also, the `begin`-`end` method won't work if there's a `create` Statement (e.g. requires an explicit `go` prior to it). – Tom Jun 23 '17 at 03:13

BlueRaja - Danny Pflughoeft · Answer 5 · 2011-07-14T03:56:14.027

9

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.

edited Jul 14 '11 at 03:56

answered Jun 20 '11 at 19:06

BlueRaja - Danny Pflughoeft

84,206
33
197
283

7

If the first conditional is "if this column doesn't exist", the first statement in the block is "add this column", then the second check of the conditional will find the column and not execute the second statement, – Damien_The_Unbeliever Jul 14 '11 at 04:09
@Damien: True; fortunately, that will never happen in my case (the conditional is always a check for a specific value in a specific table, which is always added as the last statement of the `IF` block). It seems as though there is just no good way to do this in SQL. – BlueRaja - Danny Pflughoeft Jul 14 '11 at 04:33
Mina Jacob's `set noexec` Answer is the ONLY *practical* Answer so far for use in a SS `SQLCMD` Mode Script (i.e. a master deployment script) that calls (via `:r` command) other SS Scripts (i.e. sub-deployment scripts) with some of those calls inside `if` Statements. Oded's, mellamokb's and Andy Joiner's Answers of enclosing *all* those Statements in `exec` Calls / `begin`-`end`'s are non-starters. Also, the `begin`-`end` method won't work if there's a `create` Statement (e.g. requires an explicit `go` prior to it). – Tom Jun 23 '17 at 03:15

score 2 · Answer 6 · answered Sep 09 '19 at 11:32

2

You may try this solution:

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

--if upper code not true

ALTER...
GO
UPDATE...
GO

answered Sep 09 '19 at 11:32

Luk

71
1
2

1

Not very useful if you have several if-else blocks one after the other, right? – Jerry Sep 30 '19 at 12:48

score 0 · Answer 7 · answered May 09 '19 at 20:50

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

score -1 · Answer 8 · edited Nov 02 '12 at 16:42

-1

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

edited Nov 02 '12 at 16:42

keithxm23

1,280
1
21
41

answered Nov 02 '12 at 16:17

jim a

15
1

7

It seems that LABELs cannot be referenced across GO statements since they are not in the batch sent to SQL – berkeleybross Jun 26 '14 at 14:16

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

8 Answers8

Linked

Related