Do I need to use a try..catch block, and explicit rollback in a SQL Server procedure?
If I am coding a SQL Server (2008r2) procedure, and I wrap it in a transaction, do I need to explicitly enclose it in a try..catch block, and then explicitly call rollback in the catch block, or will it exit and rollback the same on its own?
How does this:
begin transaction begin try delete from.... insert into... end try begin catch rollback transaction return end catch commit transaction
begin transaction delete from.... insert into... commit transaction
Thank you for any help.
3 Solutions collect form web for “Do I need to use a try..catch block, and explicit rollback in a SQL Server procedure?”
The answer to your question depends on the
SET XACT_ABORT setting:
Specifies whether SQL Server automatically rolls back the current
transaction when a Transact-SQL statement raises a run-time error.
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a
run-time error, the entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL
statement that raised the error is rolled back and the transaction
continues processing. Depending upon the severity of the error, the
entire transaction may be rolled back even when SET XACT_ABORT is OFF.
OFF is the default setting.
Compile errors, such as syntax errors, are not affected by SET
For example, try the following code. The first division by 0 raises an error but continues execution. The second division by zero raises an error hand halts execution:
begin transaction set xact_abort off select 1 / 0 -- causes divide by zero error, but continues select @@trancount -- returns 1 set xact_abort on select 1 / 0 -- causes divide by zero error and terminates execution select @@trancount -- we never get here rollback
If XACT_ABORT is ON, then errors will abort the transaction, and you don’t need a TRY / CATCH.
If XACT_ABORT is OFF, you will need to check the status of each statement to see if an error occurred:
begin transaction delete from... if @@error <> 0 begin if @@trancount > 0 rollback return end insert into... if @@error <> 0 begin if @@trancount > 0 rollback return end commit
However, if you ever find a case where you need to TRY / CATCH, you may need to do something special when the error occurs. If so, don’t forget to TRY / CATCH the exception handling:
begin transaction set xact_abort on begin try select 1 / 0 -- causes divide by zero error and terminates execution select @@trancount -- we never get here commit end try begin catch select xact_state() -- this will be -1 indicating you MUST rollback before doing any other operations select @@trancount -- this will probably be one, because we haven't ended the transaction yet if xact_state() <> 0 begin try select 'rollback' rollback -- do something to handle or record the error before leaving the current scope select 'exception processing here' --insert into... end try begin catch -- ignore rollback errors end catch end catch
rollbacks will occur automatically if there is an error IN MOST CASES BUT NOT ALL
if you want to guarantee a rollback for all errors precede the begin transaction with SET XACT_ABORT ON
Best practice is to explicity catch errors with a try-catch block and take action there, including perhaps a rollback and reporting/logging the error.
It depends on the severity level of the error. Sufficiently high — 16, perhaps? — the process may stop at the failing line, leaving the transaction open and your locks in place. If there’s any chance of error within a transaction, you definitely want to wrap it in the try-catch block, as you did in your first example.