3

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Programmer
  • 61
  • 2
  • 3
  • 5
  • http://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. – Yatrix Oct 24 '12 at 17:07
  • Does this answer your question? [SQL Server TRY CATCH FINALLY](https://stackoverflow.com/questions/25789859/sql-server-try-catch-finally) – Michael Freidgeim May 24 '22 at 19:02

3 Answers3

3

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.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • 1
    + 1 for providing the link to `classic TheDailyWTF`, which demonstrates misunderstanding about the `finally` block. – Devraj Gadhavi Apr 18 '13 at 07:30
  • 2
    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. – drigoangelo Jan 10 '14 at 11:25
  • The OP won't be the only person to read this, and hardware failures can be caused by more mundane things than missiles. – Mike Sherrill 'Cat Recall' Jan 10 '14 at 17:09
  • @MikeSherrill'CatRecall' Then say that. We don't wan to turn this into something that looks like GITHUb – GregJF Oct 01 '20 at 22:30
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;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
D. Lambert
  • 1,304
  • 6
  • 12
  • 1
    But, unfortunately, there's no `BEGIN FINALLY` and `END FINALLY` (yet) :-( – marc_s Oct 24 '12 at 17:11
  • 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. – D. Lambert Oct 24 '12 at 17:13
  • 3
    You should not use 18 as severity. "Indicates a problem in the Database Engine software": http://msdn.microsoft.com/en-us/library/ms164086.aspx Just reuse the value of ERROR_SEVERITY() – ildanny May 02 '19 at 15:09
  • the ultimate guide to error handling must be http://www.sommarskog.se/error_handling/Part1.html – Henrik Staun Poulsen Feb 25 '20 at 10:46
0

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.

AntDC
  • 1,807
  • 14
  • 23