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

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.

  • Performance Overhead on SQL Server Temp Tables
  • XML as parameter in stored procedure (sql server)
  • restore tabs after reopening SQL Server Management Studio
  • recycle time reporting service
  • update a database table field with comma separated list from join
  • Is there any way I can get .net stack traces in Sql Profiler, or a similar tool?
  • Is there a solution for that? (similar to finally block on try/catch of OOPS languages).

    Thanks in advance

    3 Solutions collect form web for “Finally Clause in SQL Server Transaction? Something that will execute irrespective of success or failure?”

    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.

    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.

            -- 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;
            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
        RAISERROR(N'Error occurred; rolling back and terminating.',18,1);

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.