1

I have the following procedure. (Syntax is not perfect, just want to give you guys an idea).

begin tran

begin try

while loop
  some condition, if true
   raiseerror('error', 16, 1)

end try
begin catch
  if transaction > 0
    rollback tran
  select error message
end catch

if transaction > 0
  commit tran

Questions:

  1. Will the raise error force the catch block? If so, will the transaction be rolled back completely (for all iterations of the loop)?

  2. I want #1 to be the case, do I need to add a "RETURN" after rollback tran?

I'm asking this question because it seems like the loop continued even after some error in the iteration.

JJ.
  • 9,580
  • 37
  • 116
  • 189
  • 1
    First things first....you shouldn't be using a loop to modify data in almost all situations. But yes of course if you issue a rollback it rolls back the entire transaction. If you were doing a transaction each pass of the loop then your transaction scope would be the body of the loop. You should put your commit after the loop as the last statement before "end try". There is no need to wrap it with an if statement. – Sean Lange Jul 27 '17 at 19:39
  • It's complex. Which statements can fail and what the proper recovery steps are are different in different cases and contexts. For instance, most SQL developers may not be aware that `COMMIT` can fail, but it can. – RBarryYoung Jul 27 '17 at 19:54
  • @SeanLange I had times where only some were rolled back - there's a reason why they have https://learn.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql – Eli Jul 27 '17 at 20:34
  • 1
    @Rj. if you're looking for a way to accomplish having numerous try/catch blocks, all contained in a single transaction, with an all or nothing rule (if anything fails, roll it all back), please let me know, as I have done that, and answered a question on SO with the basic code structure. – Eli Jul 27 '17 at 20:35
  • @Eli, yes, that's what I'm trying to accomplish. – JJ. Jul 27 '17 at 20:40
  • Excellent point @Eli. – Sean Lange Jul 27 '17 at 21:36

1 Answers1

1

Take a peek at this question (asked by yours truly): SQL Server XACT_ABORT with exclusion

While the question was slightly different, it shows the basic structure of how to accomplish what you're trying to do.

The 30-second rundown would be as follows: use try/catch blocks as you have done, and have a section called "failure" at the end of your procedure towards which all catches will direct. You can set a message in each catch for your logging, or pass the error along to an end user.

Eli
  • 2,538
  • 1
  • 25
  • 36