Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'
<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?

share|improve this question
2  
I'm not seeing the "Outer handler.." error on SQL Server 2012, but I am seeing it on SQL Server 2008 R2 Express. –  gbn Sep 6 '12 at 12:43
    
Does this help too? stackoverflow.com/a/2074139/27535 –  gbn Sep 6 '12 at 12:51
1  
@gbn When specifically applied to transactions, yes, it does (the template is going to be a bit more complex though, as we have sort-of-okay errors that give 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 after commit_and_exit: is going to execute twice. –  GSerg Sep 6 '12 at 13:01
    
@gbn Are you saying this is a bug in 2008 that has been fixed in 2012? Is there an entry on connect? –  GSerg Sep 6 '12 at 13:33
2  
show 3 more comments

1 Answer 1

up vote 9 down vote accepted

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

share|improve this answer
    
Makes sense to me. The context of a try block erroneously survives, so the error functions can return their values, and another consequence would be that an error brings execution into the handler of the erroneously survived try context. –  GSerg Sep 6 '12 at 14:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.