CODEWITHSUNDEEP
sql-server
JoHa
JoHa

Reputation: 2039

Rolling back the whole procedure (all statements)

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.

Upvotes: 5

Views: 158

Answers (3)

JustBoo
JustBoo

Reputation: 1741

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. :-)

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453067

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.

Upvotes: 4

Tobiasopdenbrouw
Tobiasopdenbrouw

Reputation: 14039

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.

Upvotes: 2

Related Questions