Considder the following SP:

BEGIN TRAN

UPDATE mytable
  SET Name = @Name1
  WHERE Id = @Id1

UPDATE mytable
  SET Name = @Name2
  WHERE Id = @Id2

UPDATE mytable
  SET Name = @Name3
  WHERE Id = @Id3


COMMIT TRAN

Id is PK.
Lets say there is a match for Id = @Id1 and for Id = @Id3, but there are no matches for Id = @Id2. If this is the case I want to roll back the first update and not preceed with the 3rd update, then I want to report back to the application that either 0 records were affected or maybe even better that an exception was raised.

How can I caputure if any records where affected after each update and continue if 1 row was affected and roll back and not proceed if 0 rows were affected?

Any assistance on this would be greatly appreciated.

Einar