Thanks...

What is your take on SET XACT_ABORT? Do you use it?

Also, I do have a SP that calls other SP's. The main SP that will call the other SP's begins a transactions, and each other SP begins a transaction and either rollsback or commits their respective trans. The calling SP then either rollsback or commits its trans. Is this too many trans?

I'm doing it this way because the "other" SP's may get called independently and I would like to control the trans from within each "other" or smaller SP's. Is this correct?

Thanks again...
Mike


>>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
>
>We take a different approach to things than Mike and Eric. We generally do not "nest" transactions. If I have a procedure that uses 10 SP's, there is generally one that drives them, and it is the one that will start and commit the transaction.
>
>However, our error handling calls for the SP where the error occured to Rollback the transaction, log the error, and then pass the ErrorCode to the calling SP. If an ErrorCode (<>0) is returned, the SP just goes continues to pass the error code up the calling stack until the error is returned to the Client.
>
>We got most of out ideas from and article, Error Handling in T-SQL: From Casual to Religious, in SQL Professional from pinnacle publishing.
>
>BOb