Add FINALLY clause for TRY-CATCH block - by Ralph Flora

Status : 

 


28
0
Sign in
to vote
ID 296760 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 9/5/2007 5:44:58 AM
Access Restriction Public

Description

I don't know how analogous the internal implementation of a TRY-CATCH block in T-SQL is to that of other languages such as the .NET framework languages, but it would be convenient to have a FINALLY block to add unconditional cleanup code to.  Currently, to work around the issue, I copy and paste the cleanup code into both the end of the TRY block and in the CATCH block.  However, as we all know, copy and paste programming is a horrible method of code reuse.
Sign in to post a comment.
Posted by Randy in Marin on 11/29/2011 at 4:54 PM
If there is an unhandled error in the CATCH block of a nested stored procedure, a batch abort would not permit execution of the FINALLY. However, if not nested, the unhandled error's statement level abort would permit the FINALLY to execute. The normally abnormal flow of control will not permit the FINALLY to always execute. I imagine this is why FINALLY has not been added yet.

When using a FINALLY, should the TRY-CATCH-FINALLY have to use statement level aborts only? Or perhaps flow will remain almost the same and a delayed batch abort will be used when nested so that the FINALLY can have a chance to run? (Not really an abort then, though. The nested batch would get aborted on the first unhandled error in the FINALLY or the END FINALLY statement?)

The CATCH is not executed if the error results in a connection abort. I think FINALLY would have to behave the same. So, it appears that FINALLY will not be executed in all cases - something to stress from the start.

The FINALLY block will be mostly useless unless control flow is carefully analyzed and dealt with in its implementation.

The item 709647 might relate: <https://connect.microsoft.com/SQLServer/feedback/details/709647/set-statement-abort-on-option-for-stored-procedures>
Posted by Theo Ekelmans on 5/26/2011 at 12:03 AM
I would like to be able to use the try-catch-finally and I’d like to have a continue block as well, which give much cleaner code.

while x < 20
begin
    begin transaction
    begin try
        Yiddi yadda -- profound SQL statement
    end try
    begin catch -- executed on error    
        rollback transaction
        break -- Exit loop
    end catch
    begin continue -- executed on NO error
        commit transaction
    end continue
    begin finally -- Allways executed
        set x = x + 1
    end Finally

end
Posted by Microsoft on 12/11/2007 at 1:49 PM
Hello

Thanks for your feedback and feature request. Adding a FINALLY clause to the TRY…CATCH construct is extremely useful functionality. Though this functionality may not be available in SQL Server 2008, we are considering supporting it in a future release of SQL Server.

Thank you.

SQL Server Engine Team