Why Bother?


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.




Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL &  .NET
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET
ISBN: 0072262281
EAN: 2147483647
Year: 2006
Pages: 165

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net