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

Yep, that'll work. As long as you don't expect nested transactions to do anything (not counting save points). Another thing you can do, is in the SP that could be called from a trans or independently is have it check and set a variable:

IF @@TRANCOUNT = 0
BEGIN
BEGIN TRANSACTION
SET @BEGANTRANS = 1
END


Then, at the end of your SP do:

IF @BEGANTRANS = 1
BEGIN
COMMIT
END

BOb





>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