Transaction handling in Nested Stored Procedures

Hi All

I have used nested stored Procedures on 3 level down. I am using Begin Tran..Commit Tran in each of SPs. But problem is if transaction is committed in 3rd level or 2nd level SP and error occurred in 1st level SP then the committed transactions in 2nd or 3rd level SP must be rollback. And it is not happening rather data was saved by committed Tran in 2nd or 3rd level SPs.

HHow do I handle this situation?

Thank in Advance

Following should work

Create procedure usp1
as

declare @rtnvalue int
begin tran

exec @rtnvalue = usp2
if rtnvalue <> 0 goto error_rtn

insert into table1 values ( 1) ...
if @@error <> 0 goto error_rtn

if @@trancount > 0
commit
return 0

error_rtn:
if @@trancount > 0
rollback
return -1

go

Create procedure usp2
as

declare @rtnvalue int
begin tran

exec @rtnvalue = usp3
if rtnvalue <> 0 goto error_rtn

insert into table2 values ( 1) ...
if @@error <> 0 goto error_rtn

if @@trancount > 0
commit
return 0

error_rtn:
if @@trancount > 0
rollback
return -1
go

Create procedure usp3
as

declare @rtnvalue int
begin tran


insert into table3 values ( 1) ...
if @@error <> 0 goto error_rtn

if @@trancount > 0
commit
return 0

error_rtn:
if @@trancount > 0
rollback
return -1






M A Srinivas

masri999.. I am using nested procedures...calling another procedure from 1st procedure and then again calling another procedure from second called procedure.

staya.. This is for SQL 2000...any example, refer, site??

Thanks in advance

thanks satya

refer to your reply

quote:
For example, a stored procedure, named PROC1, is called inside another stored procedure, named PROC2. The execution process of stored procedure PROC2 does not end if a fatal error occurs in PROC1. However, the execution of a stored procedure in PROC2 will end.
If any fatal error occurs in PROC1, PROC2 continues the execution.

what happen if transaction are committed in the PROC2.. are those saved in the database. if yes then how can avoid/handle this situation?

Thanks
sonny