Finally Clause in SQL Server Transaction? Something that will execute irrespective of success or failure?

Question

In SQL Server, is there something similar to finally clause in try..catch... block of c# ?

I mean, I am using BEGIN TRAN, END TRAN, COMMIT TRAN, ROLLBACK TRAN etc in a SQL Server transaction and want a section or some set of actions that needs to fire irrespective of success or failure or transaction.

Is there a solution for that? (similar to finally block on try/catch of OOPS languages).

Thanks in advance

Gateshead

technet.microsoft.com/en-us/library/ms175976.aspx There isn't anything as of this time. As long as the query doesn't bomb out, you should be able to just add code after the END CATCH to do what you need. – Underprop

Does this answer your question? SQL Server TRY CATCH FINALLY – Doublestop

Answer 1

There isn't anything that will run "irrespective of success or failure" with 100% reliability. It doesn't matter whether you're talking about the "finally" clause of a C# program, or a BEGIN TRY...END TRY BEGIN CATCH...END CATCH stucture on SQL Server.

The problem is that terminal blocks like these can't execute under every possible failure mode. Every possible failure mode has to include software, hardware, and network failures. If your client is the government, it probably has to include missle attacks, too.

Obligatory reference to a classic TheDailyWTF.

Plea

+ 1 for providing the link to classic TheDailyWTF, which demonstrates misunderstanding about the finally block. – Encourage

I am quite sure the question wasn't about hardware-wise problems. The asker just wants to know if it is possible to make the same behavior of finally blocks in c# in T-SQL. Also, if the server is attacked by missiles, government will have some more serious things to think of than a few lines of code that weren't executed. – Sparry

The OP won't be the only person to read this, and hardware failures can be caused by more mundane things than missiles. – Rubricate

@MikeSherrill'CatRecall' Then say that. We don't wan to turn this into something that looks like GITHUb – Coimbra

Answer 2

There is, in fact, a BEGIN TRY... END TRY...BEGIN CATCH... END CATCH structure in SQL Server. I use it quite frequently.

Here's an overview -- the bit about selecting error information is optional, of course -- do what makes sense in your case.

BEGIN TRY
        -- do something here.

    /*  Following line can be used to force termination for testing purposes.
        No data changes will be committed.
    */
    --RAISERROR('testing', 99, 1);

    PRINT 'Successful completion; committing transaction.';
    COMMIT TRAN;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    RAISERROR(N'Error occurred; rolling back and terminating.',18,1);
END CATCH;

Leatherette

But, unfortunately, there's no BEGIN FINALLY and END FINALLY (yet) :-( – Iguana

Yes, there's no FINALLY clause, but the question also asks about TRY.. CATCH with respect to transactions. It's a little bit of work, but I can handle most scenarios needing FINALLY given TRY and CATCH. – Leatherette

You should not use 18 as severity. "Indicates a problem in the Database Engine software": msdn.microsoft.com/en-us/library/ms164086.aspx Just reuse the value of ERROR_SEVERITY() – Entail

the ultimate guide to error handling must be sommarskog.se/error_handling/Part1.html – Brahear

Answer 3

That last one is going to raise an error every time isn't it.

If you want to trap the rollback error - you'll need to put that in a try..except block also.

Cripple