Error Handling Based on Error

Error Handling Based on @@Error

In earlier versions of SQL Server, the only way to detect an error in Transact-SQL code was to test the @@ Error function. The basic idea was to place error handling in critical positions in the code. The result would be something like the following:

      Create Procedure pr!nsertLeasedAsset_2      -- Insert leased asset and update total in LeaseSchedule.      -- (demonstration of not exactly perfect 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 Inventory(EqId,          LocationId,                      StatusId,              Leaseld,                      LeaseScheduleId,       OwnerId,                      Lease,                 AcquisitionTypeID)      values (       @intEqId,       @intLocationId,                      @intStatusId,          @intLeaseId                      @intLeaseScheduleId,   @intOwnerId,                      @mnyLease,             @intAcquisitionTypeID)      If @@error <> 0      Begin          Print 'Unexpected error occurred!'          Rollback transaction          Return 1      End      -- update total      update LeaseSchedule      Set PeriodicTotalAmount = PeriodicTotalAmount + @mnyLease      where LeaseId = @intLeaseId      If @@error <> 0      Begin           Print 'Unexpected error occurred!'           Rollback transaction           Return 1      End      commit transaction      return 0 

One serious limitation of the @@ Error function is that it changes its value after each Transact-SQL statement. In the preceding stored procedure, if an error occurred in the Insert statement, the If statement that tests the value of the @@ Error function will trap it. Unfortunately, the value of @@ Error will immediately be reset to zero because the second If statement executes successfully.

Error Handling Architecture: Based on @@Error

There are ways to use @@ Error more consistently. I have described them in a previous edition of this book and in an article in SQL Server Professional, "Error Handling in T-SQL: From Casual to Religious." You can find it on MSDN:

http://msdn.microsoft.com/library/default,asp?url=/library/en-us/dnsqlpro2k/html/sq100f15.asp

You should definitely read the article (and the previous edition of this book) if you are planning to work on SQL Server 2000 or earlier versions, but they are also useful if you want to go deeper into error handling techniques in the current version of SQL Server.