SQL Error Handling Help: While loop with tran with try catch

Question

Sorry for the horrid title...it's not very easy to explain what I'm asking in one line.

I want to run two dependent DML queries in a loop...if either of the queries fail/throw an error...I want to rollback the transaction, exit the loop, and terminate the entire proc while throwing an error (so jobs will detect the failure). I think what I have is correct...but I have a few questions because I want to better understand how it works. I've read the Microsoft documentation...but I'm still unclear on some things.

I know by using SET XACT_ABORT ON; that it will handle the rollback for the tran. Does that mean I do not need to check for IF (@@TRANCOUNT > 0) in the CATCH block?

The other question...the reason I have the TRY...CATCH block is because of the WHILE loop...I'm not sure if the failed transaction will also terminate the proc, so I'm forcing it with THROW?

Here's what I have: (Ignore the fact that it's an infinite loop, I'm not including the break logic to keep the example simple)

SET XACT_ABORT ON;
WHILE(1=1)
BEGIN
    BEGIN TRY
        BEGIN TRAN;
            --DML Query 1
            --DML Query 2
        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        IF (@@TRANCOUNT > 0)
            ROLLBACK TRAN;

        THROW;
    END CATCH
END

UPDATE

Okay I'm trying to figure out how to test it myself, I was having a hard time figuring out how to test it, but I think I've got it now. So now I feel bad for posting the question :D

Here's the test I've got so far...I'll update it as I make changes to it. It appears that the IF (@@TRANCOUNT > 0) in the CATCH block is not necessary, because when I remove the ROLLBACK and check for a transaction after a failure...there's no open transaction. However, if I leave it in...the IF statement resolves to true and it still runs the rollback without error.

SET NOCOUNT ON;

IF OBJECT_ID('dbo.ChadTestTable1') IS NOT NULL DROP TABLE dbo.ChadTestTable1; --SELECT * FROM dbo.ChadTestTable1
CREATE TABLE dbo.ChadTestTable1 (TestField VARCHAR(10) NOT NULL)

IF OBJECT_ID('dbo.ChadTestTable2') IS NOT NULL DROP TABLE dbo.ChadTestTable2; --SELECT * FROM dbo.ChadTestTable2
CREATE TABLE dbo.ChadTestTable2 (TestField VARCHAR(10) NOT NULL)

INSERT INTO dbo.ChadTestTable1 (TestField) VALUES ('Test1')
INSERT INTO dbo.ChadTestTable2 (TestField) VALUES ('Test1')

SET XACT_ABORT ON;
WHILE(1=1)
BEGIN
    BEGIN TRY
        BEGIN TRAN;
            RAISERROR('Update first table',0,1) WITH NOWAIT;
            UPDATE dbo.ChadTestTable1 SET TestField = 'Test3'

            RAISERROR('Update second table',0,1) WITH NOWAIT;
            UPDATE dbo.ChadTestTable2 SET TestField = NULL

            RAISERROR('Updates done',0,1) WITH NOWAIT;
        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        --It appears this isn't necessary...but if it's here, it still resolves to true and runs?
        IF (@@TRANCOUNT > 0)
        BEGIN
            RAISERROR('Rolling back transaction',0,1) WITH NOWAIT;
            ROLLBACK TRAN;
        END

        RAISERROR('Throwing Error',0,1) WITH NOWAIT;
        THROW;
    END CATCH

    RAISERROR('End of loop',0,1) WITH NOWAIT;
    BREAK;
END

SELECT * FROM dbo.ChadTestTable1 ctt
SELECT * FROM dbo.ChadTestTable2 ctt

user_3474677 · Answer

So I figured out my own answer. I was having a fairly difficult time trying to figure out how to test this myself which is why I posted up the question, but I eventually figured it out. Here's what I found...90% of it you can probably find in Microsoft Documentation, but I read that, and was still left confused, so I resulted to bruteforce testing.

Here's what I found that I was missing after reading the documentation:

XACT_ABORT ON - Not only does it rollback the transaction, but it will also throw a terminating error...aka, if it's on and a transaction is in a loop, and an error is thrown in the tran, it will rollback the tran, break the loop, and it will also end the proc and nothing after it will be run. The proc/script ends there.

Therefore...I don't need the TRY...CATCH block in order to run the THROW and I don't need to rollback because it will be done automatically. I'm happy to have found this because that simplifies my code quite a bit.

SQL Error Handling Help: While loop with tran with try catch

UPDATE

1 Answers