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

Answer 1

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;

Answer 2

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.

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.