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.

Answer 1

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.

Answer 2

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.