Rolling Back A single Transaction in Nested Transactions

[gana78]

Hi

Im trying to rollback a single transaction in a nested transactions. The problem is complicated due to the recursive nature of the stored procedure.

this is a highly simplified version of the issue:
drop procedure spu_test_tran

create procedure spu_test_tran @d int
as
declare @name varchar(5)
set @name = 'tran' + (convert(char,@d))
begin transaction @name
if (@d = 1)
exec spu_test_tran 2

print @name
print @@trancount
rollback tran @name
return

exec spu_test_tran 1


My actual stored procedure needs to rollback on an error. But it keeps giving messages that the number of begin and rollback/commit is not equal. Can anyone tell me what i can do for this?

Thanks

Gana - jv_ganesh@yahoo.com

 

[Cagliostro]

Create a global variable and set it on error.
If the variable indicates error, rollback. John Fill
ivfmd@mail.md