Thanks for the response... And thanks for the code...

In the following code I have a SP that adds a new account and this SP calls the "worker" SP to add a new transaction record. The "worker" sp has a trans because this SP is not the only way I call it.

So if I understand you correctly, you would duplicate the "worker" SP and remove the trans from within it and then this "caller" SP would completely control the trans? And the original "worker" that has a trans I can still call it independently and have the benefit of a trans.

What is the criteria for deciding whether to use a trans or not?

Thanks for the help
Mike


----------------------------------------------------------
Here are my SP's

--------------------------
-- CALLER SP
--------------------------

BEGIN TRANSACTION
-- Insert the Account Information
INSERT INTO dbo.accounts (email, first, last, phone, fax, company, address1, address2, city, state, zip, country_id)
VALUES (@strEmail, @strFirst, @strLast, @strPhone, @strFax, @strCompany, @strAddress1, @strAddress2, @strCity, @strState, @strZip, @strCountry)
SELECT @intAccountID = @@IDENTITY
SET @intError = @@ERROR
IF @intError != 0 GOTO __cleanup

IF (@intAccountID > -1)
BEGIN
-- Insert the credit card information
EXEC @intError = dbo._CreditCard_Insert @intAccountID, @strCCType, @strCCNumber, @strCCMonth, @strCCYear, @strCCName
IF @intError != 0 GOTO __cleanup

IF @strSetupType = 'Express'
-- Insert the Transaction Records
BEGIN
EXEC @intError = dbo._Trans_Insert @intAccountID, 'Opened TrafficEvent Account (Express)', 0, '', 'account_open', 1
IF @intError != 0 GOTO __cleanup
EXEC @intError = dbo._Trans_Insert @intAccountID, 'Express Setup Fee', @strSetupFee, '', 'fee_setup', 1
IF @intError != 0 GOTO __cleanup
EXEC @intError = dbo._Trans_Insert @intAccountID, @strDepositDesc, @strDepositAmount, @strCCNumber, 'deposit', 0
IF @intError != 0 GOTO __cleanup
EXEC @intError = dbo._Trans_Insert @intAccountID, 'Monthly ClickThrough Charge', 0, '', 'fee_clicks', 1
IF @intError != 0 GOTO __cleanup
EXEC @intError = dbo._Trans_Insert @intAccountID, 'Monthly TrafficLink™ Charge', 0, '', 'fee_trafficlink', 1
IF @intError != 0 GOTO __cleanup
END
ELSE
-- Insert the Transaction Records
BEGIN
EXEC @intError = dbo._Trans_Insert @intAccountID, 'Opened TrafficEvent Account', 0, '', 'account_open', 1
IF @intError != 0 GOTO __cleanup
EXEC @intError = dbo._Trans_Insert @intAccountID, @strDepositDesc, @strDepositAmount, @strCCNumber, 'deposit', 0
IF @intError != 0 GOTO __cleanup
EXEC @intError = dbo._Trans_Insert @intAccountID, 'Monthly ClickThrough Charge', 0, '', 'fee_clicks', 1
IF @intError != 0 GOTO __cleanup
EXEC @intError = dbo._Trans_Insert @intAccountID, 'Monthly TrafficLink™ Charge', 0, '', 'fee_trafficlink', 1
IF @intError != 0 GOTO __cleanup
END
END

GOTO __cleanup

__cleanup:
BEGIN
IF @intError != 0
BEGIN
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
EXEC dbo._Errors_Insert @@ERROR, '_Account_Insert'
END
ELSE
BEGIN
IF @@TRANCOUNT > 0 COMMIT TRANSACTION
END
END
SET XACT_ABORT OFF

RETURN CASE WHEN @intError = 0 THEN 0 ELSE -1 END

--------------------------
-- WORKER SP (inner SP being called)
--------------------------
BEGIN TRANSACTION
INSERT INTO dbo.trans (account_id, description, amount, ccnumber, type, approved) VALUES (@intAccountID, @strDescription, @strAmount, @strCCNumber, @strType, @strApproved)
SET @intError = @@ERROR
BEGIN
IF @intError != 0
BEGIN
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
EXEC dbo._Errors_Insert @@ERROR, '_Trans_Insert'
END
ELSE
BEGIN
IF @@TRANCOUNT > 0 COMMIT TRANSACTION
END
END
SET XACT_ABORT OFF

RETURN CASE WHEN @intError = 0 THEN 0 ELSE -1 END






>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