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
Semper ubi sub ubi.