CATCH block is triggered when it should not

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


GOTO statements can be used to jump to a label inside the same TRY or CATCH block or to leave a TRY or CATCH block.

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'



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'



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

It looks like the pre SQL Server 2012 behaviour is a bug.

I infer this from this Connect article:

https://connect.microsoft.com/SQLServer/feedback/details/712003/sql-server-2012-error-in-exception-handling-mechanism

I could be wrong of course...

StackExchange Database Administrators Q#23805, answer score: 10