A Proper Coding Approach

There are several approaches to solving this problem. I’ll present one approach here, but as long as you follow three basic principles of procedure transaction coding, you can use whatever method you prefer:

1.  Procedures must be able to undo all of the work performed within the procedure.

2.  Procedures must be able to manage their own transactional work properly whether they are called from a transaction or not.

3.  Procedures must make no net change to @@trancount .

My preferred approach requires changes to both the procedure and the batch that executes it. In the procedure, use a transaction savepoint to mark the beginning of the work. If there is an error, roll back to the savepoint (which has no effect on @@trancount ) and use COMMIT TRAN (to restore the nesting level after the BEGIN TRAN executed earlier in the procedure). Return a non-zero status to warn the batch that the procedure failed.

create procedure order_ins_rev           -- a procedure to insert orders
  (@item_no int,
   @qty     int)
as
declare @in_stock int
begin tran
 save tran start_work                    -- mark a savepoint
   update inventory                      -- deplete inventory first
set @in_stock = stock_qty = stock_qty - @qty
    where item_no = @item_no
   if @in_stock < 0                      -- check stock now
   begin
      rollback tran start_work
                                         -- rollback to the savepoint
commit tran                              -- restore @@trancount
      raiserror (“insufficient stock”, 16, 1)
      return 99                          -- warn of an error in the proc
end
                                         -- add the order
   insert orders ( item_no,  qty)
          values (@item_no, @qty)
commit tran                              -- commit the transaction
return 0

The batch uses the procedure return status to determine whether to continue processing. Here’s the complete execution batch, with proper error handling added.

declare @ret int
begin tran
   execute @ret = order_ins_rev 1, 6
      if @ret <> 0                          -- error check
      begin
           print “order 1 failed … quitting”
           rollback tran
           return
      end
   execute @ret = order_ins_rev 2, 4
      if @ret <> 0                          -- error check
      begin
           print “order 2 failed … quitting”
           rollback tran
           return
      end
print “order complete”
commit tran
select * from inventory
select * from orders

If processing fails, you can perform the actual ROLLBACK TRAN in the batch itself, where you are in control of the complete processing context.

Error Checking and Stored Procs

Procedures that modify data need to check the value of @@error after data modification statements in case a trappable error occurred during execution. This is particularly important when you are using transaction control statements.

Unfortunately, checking @@error is not sufficient protection from all errors. Some errors have the effect of immediately ending stored procedure execution and returning a negative (system-reserved) return status value to the calling procedure or batch. Other errors end both the procedure where the error was encountered and the procedure or batch that invoked it.