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.