Rolling back the whole procedure (all statements)

Question

How can I write the procedure in a way so that I can ROLLBACK all the INSERT, UPDATE and DELETE statements whenever ANY statement in it had an error.

Please note that my procedure might and might not have statements listed in sequence. In other words, I have an INSERT statements, then some IF logic, then a select statement, then another INSERT, then an UPDATE, followed by logic then DELETE statement, etc.

I just want to ROLLBACK all the INSERT, UPDATE and DELETE statements if error happened for any statement. I found this code http://msdn.microsoft.com/en-us/library/ms181299.aspx and http://en.allexperts.com/q/MS-SQL-Server-1801/Rollback-SP.htm

But they don't answer my question.

score 4 · Accepted Answer · edited May 23 '17 at 12:25

4

If the transaction is running with XACT_ABORT option on this will happen automatically.

set xact_abort on
begin tran

/*Your code*/


commit

Here's quite an interesting question discussing using this vs structured error handling.

edited May 23 '17 at 12:25

Community

1
1

answered Aug 07 '10 at 13:27

Martin Smith

438,706
87
741
845

score 2 · Answer 2 · answered Aug 07 '10 at 13:29

Check out how TRANSACTION works (the link you give also uses it, but there transaction seems bugged due to DTS interaction). Basically you can roll back everything you did since you signalled that you're starting your transaction.

Martin Smith's answer with xact_abort is desribed in more detail here.

JustBoo · Answer 3 · 2010-08-07T14:33:52.737

What you are describing is called "Database Transaction Processing." It creates a construct that is "all or nothing" in terms of writing data to your DB.

It creates a logical frame where all your tables are updated together, but if one table cannot update then the entire transaction is rolled back to a previous state.

Most DB's do it this way. Pseudo-code follows.

Begin transaction
    Update Table One
    Update table Two
    [Update lots of tables]
    if all updates successfull
        commit (All changes to all tables)
    else
        rollback (All changes)
End transaction

Note that the terms "transaction", "commit" and "rollback" are typically the technical terms used by most DB's as well.

Link that might explain it better than I am. http://www.subbu.org/articles/nuts-and-bolts-of-transaction-processing

Look in your DB's docs and you should find references to all this.

HTH, if not comment and I'll try again. :-)

Rolling back the whole procedure (all statements)

3 Answers3