Can we use GO several times in an SQL transaction?

Question

Is it possible to use the GO statement several times in SQL Transaction . I have a long T-SQL script and I want to run it in SQL Transaction . If everything goes well, then I will do it differently, rollback.

But during the execution of this request, I received an error, for example, 'create function must be the only statement in the batch' . As I create and drop many functions and procedures in this.

I have not used GO anywhere in the script. My question is: can I use GO several times in this long script. Since GO creates a batch package, and if the package is successful the first time, but the next time it crashes, can the rollback transaction statement actually execute the rollback that was completed?

The structure of my script is as follows:

 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 push some changes from newDB to oldDB into one script.

Answer 1

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

Transactions may cover batches. TRY / CATCH blocks cannot. The CREATE / ALTER statements must be the only statement in the package (comments are not instructions, and the statements contained in the body of the function procedure are contained, however,).

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

But doing DDL inside long transactions will fail. Especially if you plan to mix it with DML. Most of the flaws I should have explored come from this combination (Xact, DDL + DML, rollback). I highly recommend against it.

The only way to safely deploy schema updates is to backup, deploy, restore from the backup if something goes wrong.

Please note that Dan recommends (dynamic SQL) because sp_executesql launches a new, internal, batch. This batch will satisfy CREATE / ALTER restrictions.

Answer 2

Please note that GO is not an SQL keyword . This is the client batch separator used by SQL Server Management Studio and other client tools.

GO does not affect transaction volume. BEGIN TRAN will start the transaction over the current connection. COMMIT and ROLLBACK will abort the transaction. You can execute as many statements as you want. GO will follow the instructions separately.

As indicated by MSDN :

The TRY ... CATCH construct cannot span multiple batches.

Thus, BEGIN TRY, END TRY, BEGIN CATCH and ENCH CATCH cannot be divided into separate parts by the GO delimiter. They should appear in one request.

If you try to include a package separator in a TRY / CATCH statement, for example, invalid SQL:

 begin try go end try begin catch go end catch 

This will result in three different queries returning 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'. 

Answer 3

GO is a good keyword to use. GO will complete the last block of code and move on to the next block. Yes, you can use multiple GOs in a statement to split it into multiple batches. But it would be better to use try / catch logic with a GO combination as you are executing transactional logic. https://msdn.microsoft.com/en-us/library/ms175976.aspx this site gives you some examples of using a hoe to use it, and if you encounter a problem, you can output this error and continue if you select .