Search code examples
sqlsql-serverraiseerror

SQL Server Try/Catch, Begin/Tran, RaiseError, Loop, & Return. What's the right order?

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.

Solution

  • 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.