0

In a stored procedure, several "update" sql statements are being called. If any of them fails, I want to roll back the previously executed update statements in the same stored procedure. Is there a way?

Allen King
  • 191
  • 1
  • 3
  • Which answer you like will depend on whether you mean, "Roll back every previous update," or "Roll back TO the previous update." Ben and I read your question differently. :) – Katherine Villyard Jul 03 '14 at 02:36

1 Answers1

2

You could wrap the whole thing in a begin tran/end tran. To quote from MSDN:

BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent. If errors are encountered, all data modifications made after the BEGIN TRANSACTION can be rolled back to return the data to this known state of consistency. Each transaction lasts until either it completes without errors and COMMIT TRANSACTION is issued to make the modifications a permanent part of the database, or errors are encountered and all modifications are erased with a ROLLBACK TRANSACTION statement.

In response to your comment, combining this with nested Try/Catch might work for you.

Katherine Villyard
  • 18,510
  • 4
  • 36
  • 59
  • I don't think this meets the OP's requirements. He wants it such that if the second one fails, roll back to just after the first. – Ben Thul Jul 03 '14 at 02:11
  • 1
    Rereading: Are you sure? He says, "If any of them fails, I want to roll back the previously executed update statements in the same stored procedure." Roll them all back, not just the first. – Katherine Villyard Jul 03 '14 at 02:29
  • In the above example, if the first fails, not execute the remaining two. If the second update fails, I want to roll back the first and not execute the third. If the third fails, roll back the first two. – Allen King Jul 03 '14 at 03:06
  • My fault; my mind inserted the word "to" as in "roll back *to* the previously executed..." – Ben Thul Jul 03 '14 at 10:18