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?

  • 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 3 '14 at 2:36
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.

  • 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 3 '14 at 2: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 3 '14 at 2: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 3 '14 at 3:06
  • My fault; my mind inserted the word "to" as in "roll back to the previously executed..." – Ben Thul Jul 3 '14 at 10:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.