Are these code snippets equivalent ('set xact_abort on' vs 'try catch rollback')?
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
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.