T-SQL error handling

An easy solution?

SET XACT_ABORT ON

SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.

Copied from the Net to save typing.

Please avoid this style:

Begin transaction
Update…
Set…
Where…
Update…
Set…
Where…
Commit transaction

Because if errors occurred while processing the second UPDATE, SQL Server would still unconditionally commit the first (successful) UPDATE.

A more common way on error handling before try {}catch {} is introduced.
The idea is that all SQL statements inside a stored procedure should be covered with error-handling code. No transaction is considered here.

Declare @ErrorCode int Select @ErrorCode = @@Error If @ErrorCode = 0 Begin    --Some statement    Update …    Select @ErrorCode = @@Error End If @ErrorCode = 0 Begin    --Another statement    Insert …    Select @ErrorCode = @@Error End If @ErrorCode = 0 Begin    --Another statement    Update …    Select @ErrorCode = @@Error End
--this is a case of calling a stored procedure
If @ErrorCode = 0 Begin    execute @ErrorCode = MyStoredProcedure parm1, param2… End … Return @ErrorCode

There are cases when you wish to read the value of some other global variables immediately after the statement, and you should handle them with the same select statement that reads @@Error. For example, you often require something like this when you're using identity columns.

Insert … Select @id = @@identity,    @ErrorCode = @@Error

Work with Transaction

Transaction processing can be perfectly integrated with this solution. At the beginning of a stored procedure (or transaction), the developer should add the following:

Declare @TransactionCountOnEntry int
If @ErrorCode = 0
Begin  
Select @TransactionCountOnEntry = @@TranCount  
BEGIN TRANSACTION
End

At the end of the procedure/transaction, the developer should complete the transaction as follows:

If @@TranCount > @TransactionCountOnEntry
Begin  
If @ErrorCode = 0     
COMMIT TRANSACTION  
Else    
ROLLBACK TRANSACTION
End

The solution will also perform well in the case of nested stored procedures. All procedures will be rolled back using the same cascading mechanism. Here, the local variable @TransactionCountOnEntry is used to track the number of opened transactions upon the entry of a stored procedure. If the number was unaffected inside the stored procedure, there's no reason to either commit or rollback inside the procedure.