On my neverending quest for shooting myself in the foot with save transaction I seem to have found even more ways to fulfill the quest purpose. The save transaction clause itself this time is out of the question, but it is because of it I wrote the code below.
Consider the following complete example with nested error handlers:
begin try
begin try
select 'Step 1';
end try
begin catch
select 'Step 1 handler - handling ''' + error_message() + '''';
goto commit_and_exit;
end catch;
begin try
select 'Step 2';
raiserror('Step 2 error', 16, 1);
end try
begin catch
select 'Step 2 handler - handling ''' + error_message() + '''';
goto commit_and_exit;
end catch;
end try
begin catch
select 'Outer handler - handling ''' + error_message() + '''';
goto commit_and_exit;
end catch
commit_and_exit:
raiserror('Error raised for the caller to see', 16, 1);
It is documented that
GOTOstatements can be used to jump to a label inside the sameTRYorCATCHblock or to leave aTRYorCATCHblock.
Or can it?
Given the code above, a sane programmer would figure the output will be
Step 1
Step 2
Step 2 handler - handling 'Step 2 error'
<Error raised for the caller to see>
In fact what is happening is:
Step 1
Step 2
Step 2 handler - handling 'Step 2 error'
Outer handler - handling 'Error raised for the caller to see'
<Error raised for the caller to see>
When debugging step by step, I can see that control leaves the try/catch blocks completely, then an error is raised, the control is returned to the outermost catch block, that block executes, the control goes to commit_and_exit: again, and that final block is executed again.
If you have some commit trans or rollback transs under commit_and_exit:, you are going to attempt to commit the tran twice. With imaginable results, given the fact there can be outer transactions started by the caller.
I also tried create another label, end_of_outer:, right before the outer end try, so that the control leaves the outer try block "normally". Interesting enough, that did not make any difference.
What the hell is going on, and what is the correct way of doing it?
XACT_STATE() = 1, so we rollback to a save point and carry on). But I'm more concerned about the very principle of error handling here, even when to transactions are present. Any code aftercommit_and_exit:is going to execute twice.