Thanks...

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

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