BEGIN TRANSACTION
UPDATE mytable
SET Name = @Name1
WHERE Id = @Id1
IF @@ROWCOUNT = 0 GOTO UpdateFailed
UPDATE mytable
SET Name = @Name2
WHERE Id = @Id2
IF @@ROWCOUNT = 0 GOTO UpdateFailed
UPDATE mytable
SET Name = @Name3
WHERE Id = @Id3
IF @@ROWCOUNT = 0 GOTO UpdateFailed
....
COMMIT TRANSACTION
RETURN
UpdateFailed:
ROLLBACK TRANSACTION
RETURN 16
>LOL Yes it looks very ugly. It looks even more ugly when I was done with it than what I thought it would when I started. 42, yes, 42 that is the snswer to everything :)
>
>OK let me try to explain what I am acctually trying to do instead of using an example.
>I have one Header table and one Detail table. Each Header record has 41 (never more and never less) child records (there is a one to many relation-ship between Header and Detail table using PK and FK pair).
>I have an SP that I want to use to update the Header table and the Detail table all in the same SP. (The Detail records should always be updated along with the Header record). I pass all the parameters I need to into the SP (don't worry it is not too bad there is only one parameter for each of the 41 child records in the Detail table).
>So now I want to issue 42 UPDATEs (1 for the Header table and 41 for the Detail table), but if one of the UPDATEs fail I want them all to fail (i.e. ROLLBACK).
>
>I hope I explained my pain well enough. Please let me know if you want additional information or if I am beeing unclear.
>
>I would love to skin the cat differently.
>Einar
>
--sb--