este de la "COMMIT TRANSACTION".

Ce nu inteleg este de ce folosesti cursoare ? daca cumva procedurile tale sunt proceduri ce intorc date, poti face atfel:
DECLARE @a table (col1 tip1, .., colN tipN)
DECLARE @b table (col1 tip1, .., colN tipN)

BEGIN TRAN

begin try

insert into @a
exec SProc

insert into @b
exec altSProc

commit tran

end try
begin catch

raiserror(....)
rollback

end catch

Adica incepi o tranzactie, se executa codul din begin try si end try si daca a ajuns la final, se executa ultima linie ...adica COMMIT. Daca pe undeva a dat eroare, intra pe catch si executa rollback.

O alta metoda este ca dupa executia procedurilor sa verifici @@error. Acolo unde ai tu in codul tau, practic, verifici daca COMMIT-ul s-a executat. @@error se seteaza la fiecare instructiune executata, adica vei testa daca a fost eroare la instructiunea precedenta.