22

Can We use GO statement mutiple times in a SQL Transaction. I am having a long T-SQL script and I want to run it in a SQL Transaction. If all goes well then I will commit otherwise will rollback.

But, While running that query I got error like 'create function must be the only statement in the batch'. As I am creating and dropping many Functions and Procedures in that.

I have not used GO anywhere in the script. My question is that - Can I use multiple times GO statement in that long script. Because, GO creates a batch and if batch executes successfully first time but fails next time then will rollback transaction statement be able to actually rollback that has been executed ?

Structure of my script looks like :

PRINT 'Transaction Started'
BEGIN TRY
    BEGIN TRAN

    Drop Function 
    ....
    ....
    Create Function
    ....
    ....
    Drop Procedure
    ....
    ....
    Lots of statements
    ....
    ....

    COMMIT TRAN
    PRINT 'Transaction Succeeded'
END TRY
BEGIN CATCH
    PRINT 'Transaction Failed'
    IF(@@TRANCOUNT > 0)
        ROLLBACK TRAN
END CATCH

I am creating this script to migrate some changes from newDB to oldDB in a single script.

Nick Krasnov
  • 26,886
  • 6
  • 61
  • 78
Ankush Jain
  • 5,654
  • 4
  • 32
  • 57
  • Are you creating a stored procedure or function in this script? – Dane Dec 09 '15 at 11:59
  • I have updated my answer. Anyways..I am creating and dropping both. First checking if they exist or not. – Ankush Jain Dec 09 '15 at 12:02
  • Try this answer here: http://stackoverflow.com/a/11121382/249813 – JamesT Dec 09 '15 at 12:12
  • Yes, a transaction can span multiple batches (GO commands). However, the `TRY/CATCH` scope is the same batch and some DDL like `CREATE FUNCTION` must be in a separate batch. You'll need to use dynamic SQL to create those within the same `TRY/CATCH` block, or forego the use of `TRY/CATCH`. – Dan Guzman Dec 09 '15 at 12:23
  • So All I need to do is create views and procedures using dynamic sql. Right ? I will keep all the script in single try/catch. – Ankush Jain Dec 09 '15 at 12:27

3 Answers3

39

You are mixing concepts. GO is not a Transact-SQL concept, not part of the language, and not understood by SQL Server. GO is the tools batch delimiter. sqlcmd.exe and SSMS both are using, by default, GO as the batch delimiter. The batch delimiter is used to identify the individual batches inside the SQL source file. The client tool sends to the server one batch at a time (of course, omitting the delimiter).

Transactions can span batches. TRY/CATCH blocks cannot. CREATE/ALTER statements must be the only statement in a batch (comments are not statements, and statements contained in a function procedure body are,well, contained).

Something similar to what you want to do can be achieved by starting a transaction and abortign the execution on first error (-b at sqlcmd.exe start, or use :on error exit in SSMS).

But doing DDL inside long transactions is not going to work. Specially if you plan to mix it with DML. Most corruptions I had to investigate come from this combination (Xact, DDL + DML, rollback). I strongly recommend against it.

The sole way to deploy schema updates safely is to take a backup, deploy, restore from backup if something goes wrong.

Note that what Dan recommends (dynamic SQL) works because sp_executesql starts a new, inner, batch. This batch will satisfy the CREATE/ALTER restrictions.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • "But doing DDL inside long transactions is not going to work. Specially if you plan to mix it with DML." - why not? – MgSam Jun 18 '18 at 15:36
7

Note that GO is not a SQL keyword. It is a client-side batch separator used by SQL Server Management Studio and other client tools.

GO has no effect on transaction scope. BEGIN TRAN will start a transaction on the current connection. COMMIT and ROLLBACK will end the transaction. You can execute as many statements as you want in-between. GO will execute the statements separately.

As specified by MSDN:

A TRY…CATCH construct cannot span multiple batches.

So BEGIN TRY, END TRY, BEGIN CATCH, and END CATCH cannot be separated into separate batches by a GO separator. They must appear in the same query.

If you do try to include a batch separator in a TRY/CATCH statement like the invalid SQL below:

begin try
    go
end try
begin catch
    go
end catch

This will execute 3 different queries that return syntax errors:

1) begin try

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'begin'.

2) end try begin catch

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'try'.

3) end catch

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'catch'.
Community
  • 1
  • 1
Paul Williams
  • 16,585
  • 5
  • 47
  • 82
  • Will XACT_ABORT rollback ALL batches in case of error? – HamsterWithPitchfork Sep 27 '19 at 07:00
  • @metabuddy `XACT_ABORT` aborts all open transactions. If you have multiple `GO` statements in a batch, the client will separate the input into multiple round-trips. If an error occurs in a query that has multiple GO `statements`, the current transaction is aborted, but the batch will continue with the next batch that is after the next `GO` statement. – Paul Williams Sep 30 '19 at 13:27
2

GO is a nice keyword to use. The GO will complete the last code block and continue on to the next block. Yes you can use multiple GOs in a statement to break it up into multiple batches. But it would be better to use try/catch logic with a combination of GOs since you are doing transaction based logic. https://msdn.microsoft.com/en-us/library/ms175976.aspx this site gives you some examples on how to use it and if you run into a hitch you can output that error and continue on if you choose.

Wes Palmer
  • 880
  • 4
  • 15