When you nest transactions, just remember that you must issue a COMMIT TRANSACTION for each level but a single ROLLBACK TRANSACTION will take them all out.

You can do the following and see this behavior

BEGIN TRANSACTION
BEGIN TRANSACTION
BEGIN TRANSACTION
BEGIN TRANSACTION
PRINT @@TRANCOUNT -- 4

COMMIT TRANSACTION
PRINT @@TRANCOUNT -- 3

ROLLBACK TRANSACTION
PRINT @@TRANCOUNT -- 0

Given this, you might be better off not using nested transactions and allowing the first proc to start a transaction to be the master. You can use @@TRANCOUNT to determine if a transaction has been started:

IF @@TRANCOUNT = 0 BEGIN TRANSACTION

-Mike

>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