What you'll probably want to do is start some nested trasactions. Start a transaction in each stored proc, if it failes just rollback that transaction only, then return 0. The sp that called it can pick up that returnd 0 and roll back its transaction.

Check the BOL for nested transactions.

HTH
Eric

>Hi All,
>
>I have several stored procedures that have nested SP calls. I want to make sure all transactions are rolled back in case of an error along the way. Where each separate SP may have a BEGIN TRAN statement...
>
>What is the best way to handle @@ERROR when one SP may call another SP?
>
>TIA,
>Mike