roll back a batch is not easy?

Question

After two days tests on batch, roll back and try ..catch, my mind is still vague. I separate what I was doing into two step in order to clear my question.

1. roll back a batch As online book explains, in a batch, executed statements cannot be roll back only except the batch is in a transaction and error in the batch cause the transaction is roll back.

So I put the batch into a transaction like

begin transaction
     create table  A ...
     insert into A values...
     insert into A values... (error here!)
     insert into A values...
     GO
rollback

This works with error output and no table was created

(1 row(s) affected)
Msg 213, Level 16, State 1, Line 5
Column name or number of supplied values does not match table definition.

Msg 208, Level 16, State 1, Line 1
Invalid object name 'A'.

However, the rollback will be executed anyway even no error in transaction. In order to deal with this case, I use TRY ...CATCH as in 2.

2. use TRY ...CATCH

BEGIN TRY
   begin transaction
     create table  A ...
     insert into A values...
     insert into A values... (error here!)
     insert into A values...
     --GO
END TRY 
BEGIN CATCH
     ROLLBACK
END CATCH

This time it doesn't allow statement GO here any more. S*o Batch is whole block between BEGIN TRY AND END TRY in this case?* In addition, the result is not as I expected. The CREATE TABLE AND first insert were still executed and didn't roll back. I searched again. It seems I need to SET XACT_ABORT ON in order to record these executed statement as uncommitted before touch commit. What I am understanding here is right? If so, I didn't add any commit statement in this case.

By the way, test are done on SQL SERVER 2012. Thanks for any clarification!

possible duplicate of [SQL Server - transactions roll back on error?](http://stackoverflow.com/questions/1749719/sql-server-transactions-roll-back-on-error) — Ken White, Jul 04 '13 at 01:00
if remove GO, rollback cannot take effect, maybe because of latency of transaction — FebWind, Jul 04 '13 at 18:44

score 1 · Accepted Answer · edited May 23 '17 at 10:28

The reason it doesn't allow the GO statement is that the try and catch must be part of the same batch as metioned in this MSDN article. It states;

"Each TRY…CATCH construct must be inside a single batch, stored procedure, or trigger. For example, you cannot place a TRY block in one batch and the associated CATCH block in another batch. The following script would generate an error:"
BEGIN TRY
    SELECT *
        FROM sys.messages
        WHERE message_id = 21;
END TRY
GO
-- The previous GO breaks the script into two batches,
-- generating syntax errors. The script runs if this GO
-- is removed.
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
GO

For an alternative idea on how to handle this, take a look at gbns answer on the question Nested stored procedures containing TRY CATCH ROLLBACK pattern? as he discusses his pattern/template for handling transactions including the use of (and reason for) XACT_ABORT and other nifty features. I'd also suggest reading the associated links in gbns answer

Aaron Betrand's answer to the same question refers to a Erland Somarsskog's article on error handling that is very similar to gbn's answer also.

Even though the title of the original question relates to nested transactions, it is still applicable in your situation I believe.

score 0 · Answer 2 · answered Jul 04 '13 at 01:09

0

Try using SET XACT_ABORT ON in the begining of your script.

Check out XACT_ABORT MDSN reference page here

answered Jul 04 '13 at 01:09

andre.barata

663
3
11

roll back a batch is not easy?

2 Answers2