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-