Some observations:

1a) Rollback tran affects all active transactions. The commit is meaningless because the only transaction has just been rolled back.

1b) Code works with the save point because the rollback was specific to the save point.

2a) No problem with transactions over nested procedures.

2b) You can return codes with care.

We use a template for all stored procedures to resolve this. It is something like the following pseudo code

CREATE PROCEDURE procName

@ErrMsg varchar(100) = null output

AS

declare

@procStartedTran

@ErrorCode

@procStartedTran = false, @ErrorCode = 0

IF @@TRANCOUNT = 0

BEGIN

BEGIN TRAN

@procStartedTran = true

END

--To call other procedures

EXEC @ErrorCode = ProcCall

@ErrMsg OUTPUT

if @ErrorCode <> 0 GOTO Cleanup

--If sub-tran needed (we use them rarely)

SAVE TRAN ProcedureName

Do Work

If Failure

ROLLBACK TRAN ProcedureName

--end proc

CleanUp:

if @procStartedTran

begin

if @ErrorCode = 0

commit tran

else

rollback tran

end

return @ErrorCode

We find this resolves all our transaction and nesting problems. Errors can occur 10 levels deep and are properly returned to the calling procedure.

Hope this helps

Guarddata-