Are these code snippets equivalent ('set xact_abort on' vs 'try catch rollback')?

I used this piece of code in my stored procedure in SQL Server:

create procedure proc_name
    --declare variables
as
    set nocount on
    begin transaction
    begin try
        --do something
        commit transaction
    end try begin catch
        rollback transaction
        ;throw
    end catch
go

but today I got acquainted with the instruction "set xact_abort on". Is the following code equivalent to the previous? Are there any differences between the two?

create procedure proc_name
    --declare variables
as
    set nocount on
    set xact_abort on
    begin transaction
    --do something
    commit transaction
go
+4
source share
1 answer

Quote from MS docs

The TRY ... CATCH construct catches all runtime errors that have a severity greater than 10 that do not close the database connection.

So, a catch attempt will not catch all possible errors. You can use xact_abort in addition to try catch.

try/catch , .. , - .

+4

All Articles