Do I need to use a try..catch block, and explicit rollback in a SQL Server procedure?

Question

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?

i.e.:

How does this:

    begin transaction

    begin try
    delete from....

    insert into...
    end try
    begin catch
    rollback transaction
    return
    end catch

    commit transaction

Compare with:

    begin transaction
    delete from....

    insert into...
    commit transaction

Thank you for any help.

Answer 1

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 XACT_ABORT.

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 and 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

Answer 2

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.