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:
Will the raise error force the catch block? If so, will the transaction be rolled back completely (for all iterations of the loop)?
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.
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.