SQL Server has an equivalent to the On Error Go To command used by Visual Basic (before .NET). The Set Xact_Abort On statement forces SQL Server to roll back the complete transaction and stop further processing on the occurrence of any error. For example, execute the following script that attempts to insert one integer and one date into the integer column:
create table test (i int) GO set xact abort on begin tran print 'Begin' insert test(i) values(1) insert test(i) values('5/5/5') commit tran print 'End' GO select * from test
SQL Server will detect the problem and automatically roll back the transaction:
Begin (1 row(s) affected) Msg 245, Level 16, State 1, Line 6 Syntax error converting the varchar value '5/5/5' to a column of data type int. i ----------- (0 row(s) affected)
Unfortunately, this solution could present a potential problem. This statement will also completely stop execution of the current batch. The error can still be detected and handled from the client application, but inside the Transact-SQL code, SQL Server will treat it as a fatal error. Note that the second batch in the preceding example (the Select statement) was started and executed as though nothing had happened.
However, the Try-Catch statement can prevent batches from being terminated. In the following example, I wrapped the two Insert statements in an error handler:
set xact_abort on Begin try begin tran print 'Begin try' insert test(i) values(1) insert test(i) values('5/5/5') commit tran print 'End try' End try Begin Catch rollback tran print 'Catch' End Catch select * from test
Note that I added the Rollback statement to the Catch block. You will get the following text in the Results pane:
Begin try (1 row(s) affected) Catch i ---------- (0 row(s) affected)
You can take advantage of the fact that Set Xact_Abort On without a Try-Catch statement stops further execution. You can decide to write all of your stored procedures based on this behavior:
create Procedure dbo.ap_LeasedAsset_Insert7 -- Insert leased asset and update total in LeaseSchedule. -- (demonstration of SET XACT_ABORT ON solution) ( @intEqId int, @intLocationId int, @intStatusId int, @intLeaseId int, @intLeaseScheduleId int, @intOwnerId int, @mnyLease money, @intAcquisitionTypeID int ) As set nocount on SET XACT_ABORT ON begin transaction -- insert asset insert Inventory(EqId, LocationId, StatusId, LeaseId LeaseScheduleId, OwnerId, Lease, AcquisitionTypeID) values ( @intEqId, @intLocationId, @intStatusId, @intLeaseId @intLeaseScheduleId, @intOwnerId, @mnyLease, @intAcquisitionTypeID) -- update total update dbo.LeaseSchedule Set PeriodicTotalAmount = PeriodicTotalAmount + @mnyLease where LeaseId = @intLeaseId commit transaction return (0)
The burden of error handling for such procedures would be transferred to the developers of client applications.
Another problem is that the Set Xact_Abort statement does not detect "compilation" errors. According to SQL Server Books OnLine: "Compile errors, such as syntax errors, are not affected by Set Xact_Abort." Unfortunately, because of deferred name resolution, compilation errors can occur at runtime as well. By editing the stored procedure from the preceding example, the Update statement references a nonexistent table:
-- update total update dbo.LeaseSchedule_NON_EXISTING_TABLE Set PeriodicTotalAmount = PeriodicTotalAmount + @mnyLease where LeaseId = @intLeaseId
Next, run the stored procedure:
Exec dbo.ap_LeasedAsset_Insert8 @intEqId = 100, @intLocationId = 1, @intStatusId = 1, @intLeaseId = 1, @intLeaseScheduleId = 1, @intOwnerId = 1, @mnyLease = 5000, @intAcquisitionTypeID = 1 -- test transaction select * from Inventory where EqId = 100 and LocationId = 1
SQL Server simply stops the execution of the stored procedure without a rollback:
Server: Msg 208, Level 16, State 1, Procedure prInsertLeasedAsset_8, Line 30 Invalid object name 'LeaseSchedule_NON_EXISTING_TABLE'. Server: Msg 266, Level 16, State 1, Procedure prInsertLeasedAsset_8, Line 36 Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1. EqId LocationId StatusId Lease -------------------------------------------- 100 1 1 5000.0000
This is a potentially significant problem. The official response concerning my support question on this matter was that SQL Server is behaving as specified in SQL Server Books OnLine. Developers have different expectations—in the case of an error, the transaction should be rolled back. This explanation makes it sound like the Set Xact_Abort statement is useless. Fortunately, the stored procedure will be promoted to production only after detailed unit testing, and therefore it should not reference nonexistent tables.
However, there is an additional problem: only the stored procedure with the syntax error is aborted. Assume that the stored procedure is executed as a nested stored procedure and that the compilation error occurs in the inner stored procedure. The earlier procedure is split into two procedures to demonstrate this scenario:
Create Procedure prUpdateLeaseSchedule @intLeaseId int, @mnyLease int as update LeaseSchedule_NON_EXISTING_TABLE set PeriodicTotalAmount = PeriodicTotalAmount + @mnyLease where LeaseId = @intLeaseId return (0) GO create Procedure pr!nsertLeasedAsset_9 -- Insert leased asset and update total in LeaseSchedule. -- (demonstration of compilation error in nested stored procedure) ( @intEqId int, @intLocationId int, @intStatusId int, @intLeaseId int, @intLeaseScheduleId int, @intOwnerId int, @mnyLease money, @intAcquisitionTypeID int ) As set nocount on SET XACT_ABORT ON begin transaction -- insert asset insert Inventory(EqId, LocationId, StatusId, LeaseId LeaseScheduleId, OwnerId, Lease, AcquisitionTypeID) values ( @intEqId, @intLocationId, @intStatusId, @intLeaseId @intLeaseScheduleId, @intOwnerId, @mnyLease, @intAcquisitionTypeID) -- update total exec prUpdateLeaseSchedule (SintLeaseId @mnyLease commit transaction return (0) GO
Now run them:
Exec prInsertLeasedAsset_9 @intEqId = 200, @intLocationId = 1, @intStatusId = 1, @intLeaseId = 1, @intLeaseScheduleId = 1, @intOwnerId = 1, @mnyLease = 5000, @intAcquisitionTypeID = 1 -- test transaction select EqId, LocationId, StatusId, Lease from Inventory where EqId = 200 and LocationId = 1
SQL Server simply stops the execution of the inner stored procedure, but the outer stored procedure continues as though nothing has happened (and even commits the transaction):
Server: Msg 208, Level 16, State 1, Procedure prUpdateLeaseSchedule, Line 5 Invalid object name 'LeaseSchedule_NON_EXISTING_TABLE'. EqId LocationId StatusId Lease ------------------------------------------------- 200 1 1 5000.0000
At the time, my expectation was that the Set Xact_Abort statement would abort further execution of everything, as it does in the case of runtime errors. Unfortunately, it does not behave in that way. This is potentially very dangerous, but as I said before, problems such as this should be caught during the QA phase.
On a recent .NET project I was involved with, there were many errors when we mixed different types of transactions—COM+, DTC, ADO, and Transact-SQL. Therefore, we decided not to mix them. We went even further and decided not to nest any transactions. If the caller initiates a transaction, the nested procedure skips its own initiation of the transaction. Furthermore, the transaction should be closed only from within the procedure that initiated it. We also decided, as a rule, not to handle errors in stored procedures. The rationale was that SQL Server cannot trap all errors and that we need to write error handling in the client application (actually the middleware components) anyway.
The following procedure records the number of opened transactions on entry. The Begin Tran statement is preceded by the If statement that initiates the transaction only if the procedure is not already processing a transaction:
create procedure dbo.ap_Equipment_Insert -- insert equipment (and if necessary equipment type) -- (demonstration of alternative method for error handling and transaction processing) OchvMake varchar(SO), OchvModel varchar(SO), OchvEqType varchar(SO), OintEqId int OUTPUT AS set xact_abort on set nocount on declare @intTrancountOnEntry int, @intEqTypeId int set @intTrancountOnEntry = @@tranCount -- does such EqType already exist in the database If not exists (Select EqTypeId From dbo.EqType Where EqType = @chvEqType) --if it does not exist Begin if @@tranCount = 0 BEGIN TRAN -- insert new EqType in the database Insert dbo.EqType (EqType) Values (@chvEqType) -- get id of record that you've just inserted Select @intEqTypeId = @@identity End else begin -- read Id of EqType Select @intEqTypeId From dbo.EqType Where EqType = OchvEqType end --insert equipment Insert dbo.Equipment (Make, Model, EqTypeId) Values (@chvMake, @chvModel, @intEqTypeId) Select @intEqId = @@identity if @@tranCount < @intTrancountOnEntry COMMIT TRAN return 0
The Commit Tran statement will similarly be executed only if the transaction is initiated in the current procedure.
The following procedure demonstrates the way to return logic errors to the caller. Notice that I am using both Raiserror and Return statements. It is very important to use the Return statement to communicate an error to the caller because the caller might not be able to detect the effect of the Raiserror statement.
ALTER Procedure dbo.ap_Inventory_InsertXA -- insert inventory record , update inventory count and return Id -- (demonstration of alternative method for error handling -- and transaction processing) @intEqId int, @intLocationId int, @inyStatusId tinyint, @intLeaseId int, @intLeaseScheduleId int, @intOwnerId int, @mnsRent smallmoney, @mnsLease smallmoney, @mnsCost smallmoney, @inyAcquisitionTypeID int, @intInventoryId int output As declare (SintTrancountOnEntry int set nocount on set xact_abort on set @intTrancountOnEntry = @@tranCount if @@tranCount = 0 begin tran Insert into dbo.Inventory (EqId, LocationId, StatusId, LeaseId LeaseScheduleId, OwnerId, Rent, Lease, Cost, AcquisitionTypeID) values (@intEqId, @intLocationId, @inyStatusId, @intLeaseId @intLeaseScheduleId, @intOwnerId, @mnsRent, @mnsLease, @mnsCost, @inyAcquisitionTypeID) select @intInventoryId = Scope_Identity() update dbo.InventoryCount Set InvCount = InvCount + 1 where LocationId = @intLocationId if @@rowcount <> 1 begin -- business error Raiserror(50133, 16, 1) if @@tranCount > @intTrancountOnEntry rollback tran return 50133 end if @@tranCount > @intTrancountOnEntry commit tran return 0
The following procedure demonstrates the detection of logic errors from the nested stored procedure: create procedure dbo.ap_InventoryEquipment_Insert_XA
- - insert new inventory and new equipment -- (demonstration of alternative method for error handling -- and transaction processing) (SchvMake varchar(50), @chvModel varchar(50), @chvEqType varchar(30), @intLocationId int, @inyStatusId tinyint, @intLeaseId int, @intLeaseScheduleId int, @intOwnerId int, @mnsRent smallmoney, @mnsLease smallmoney, @mnsCost smallmoney, @inyAcquisitionTypeID int, @intlnventoryId int output, @intEqId int output as Set nocount on set xact_abort on declare @intError int, @intTrancountOnEntry int set @intError = 0 set @intTrancountOnEntry = @@tranCount if @@tranCount = 0 begin tran -- is equipment already in the database if not exists(select EqId from Equipment where Make = @chvMake and Model = @chvModel) EXEC @intError = dbo. ap_Equipment_Insert @chvMake, @chvModel, @chvEqType, @intEqId OUTPUT if @intError > 0 begin if @@tranCount > @intTrancountOnEntry rollback tran return @intError end exec @intError = dbo. ap_Inventory_InsertXA @intEqId, @intLocationId, @inyStatusId, @intLeaseId @intLeaseScheduleId, @intOwnerId, @mnsRent, @mnsLease, @mnsCost, @inyAcquisitionTypeID, @intInventoryId output if @intError > 0 begin if @@tranCount > @intTrancountOnEntry ROLLBACK TRAN return @intError end if @@tranCount @ @intTrancountOnEntry COMMIT TRAN return 0
If an error has been returned, the current stored procedure will roll back the transaction (using Rollback Transaction) if a transaction has been initiated in it. The caller stored procedure can also be designed so that it knows about all (or some) error codes that can be returned from a nested stored procedure. Then it is possible to write code that will handle the errors.
To test it, run the following:
declare @intError int, @intInvId int, @intEqId int begin tran exec @intError = ap_InventoryEquipment_Insert_XA @chvMake = 'Compaq', @chvModel = 'IPaq 3835', @chvEqType = 'PDA', @intLocationId = 12, @inyStatusId = 1, @intLeaseId = null, @intLeaseScheduleId = 1, @intOwnerId = 411, @mnsRent = null, @mnsLease = null, @mnsCost = $650, @inyAcquisitionTypeID = 1, @intlnventoryId = @intlnvId output, @intEqId = @intEqId output if @intError = 0 commit tran else rollback tran select @intError Err select * from Inventory where InventoryId = @intInvId select * from Equipment where EqId = @intEqId
In the case of an error, SQL Server returns the error message and rolls back the transaction:
Server: Mag 50133, Level 16, State 1, Procedure ap Inventory InsertXA, Line 48 Unable to update inventory count. Err --------- 50133 (1 row(s) affected) Inventoryid EqId LocationId StatusId LeaseId LeaseScheduleId ----------- ------------ ---------- -------- ------- --------------- (0 row(s) affected) EqId Make Model --------- ----------------------------------------------------- ----- (0 row(s) affected)