Listing 10: More Severe Example of Not Testing for a RETURN Status

BEGIN TRAN
EXEC SP_1

/* SP_1 Code
BEGIN tran
UPDATE table_1 …      -- Update fails.
IF @@ERROR <> 0
BEGIN
   ROLLBACK tran         -- rolls back, but resets @@ERROR to 0.
   RETURN 1
END
RETURN 0  */

IF  @@ERROR <> 0  
 -- @@ERROR will ALWAYS be 0 at this point unless error in SP_1 was of the 
untrappable type.

BEGIN  
   ROLLBACK                -- ROLLBACK statement never executed.
   RETURN 1                 -- RETURN statement never executed.
END

EXEC  SP_2                 -- Code will continue processing SP_2, which succeeds.

IF  @@ERROR <> 0
BEGIN
   ROLLBACK
   RETURN 1
END
  
COMMIT
 -- SP_2 changes committed, even though SP_1 rolled back its own changes.