>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

BEGIN TRAN

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

UPDATE mytable
  SET Name = @Name2
  WHERE Id = @Id2 AND @@ROWCOUNT > 0

UPDATE mytable
  SET Name = @Name3
  WHERE Id = @Id3 AND @@ROWCOUNT > 0

IF @@ROWCOUNT > 0
   COMMIT TRAN
ELSE
  ROLLBACK
ENDIF