4

I used to use this code snippet within 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 to know 'set xact_abort on' statement. Is the following code equivalent to previous one? Are there any differences between them?

create procedure proc_name
    --declare variables
as
    set nocount on
    set xact_abort on
    begin transaction
    --do something
    commit transaction
go
Anatoly U
  • 258
  • 1
  • 11

1 Answers1

4

Quoting from MS docs

A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection.

So, try catch does not catch all possible errors. You can use xact_abort on in addition to try catch.

try/catch give you more flexibility, i.e., you are not limited to just a rollback when something is not happy.

Gary Walker
  • 8,831
  • 3
  • 19
  • 41
  • «try catch does not catch all possible errors». And xact_abort rollbacks tran when encounters any error, including warnings with severity level <= 10 ? «try/catch give you more flexibility» - I knew this, but I don't use this flexibility in my snippet. Still this your remark is useful though. – Anatoly U Jan 09 '14 at 20:37