>Thanks...
>
>What is your take on SET XACT_ABORT? Do you use it?
>
>Mike

Yes, we set it ON in all SP's. The main reason is, since our SP's manage transaction then we need it to be rolled back on a fatal error. It seems silly to me that this is not the default. But, I guess if you were handeling your transactions from the Client, you could always send a ROLLBACK from it when you get an error.

BOb


>
>
>>>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