For many, the question is, Why be concerned with implementing error handling at all? Let us review this question through the following example:
Create Procedure dbo.ap_LeasedAsset_Insert1 -- Insert leased asset and update total in LeaseSchedule. -- (demonstration of imperfect solution) { @intEqId int, @intLocationId int, @intStatusId int, @intLeaseId int, @intLeaseScheduleId int, @intOwnerId int, @mnyLease money, @intAcquisitionTypeID int } As set nocount on begin transaction -- insert asset insert dbo.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 Leaseld = @intLeaseId commit transaction return
This may seem like a trivial example, and it is true that in all probability nothing would go wrong, but imagine that an error occurs on the Update statement. The error could be for any reason—overflow, violation of a constraint, or inadequate security privileges, for example. As explained earlier, transactions do not automatically roll back when an error occurs. Instead, SQL Server simply commits everything that was changed when it encounters the Commit Transaction statement as if nothing unusual had happened. Unfortunately, from that moment on, the total of the lease schedule will have the wrong value.