How will the transaction work with nested procedure?
In nested stored procedure: you call a procedure inside a procedure. This transaction needs to be handled in a different way. If in case a transaction is written in both the procedure and if the child procedure fails then the output of the procedure will not be handed by the parent procedure so it is recommended to not to write the transaction inside the child procedure. Same transaction will work in the child procedure which is written in parent procedure.
If no errors:
alter proc usp_Parent_proc
as
begin try
begin transaction Parent
select 'Parent Proc Started'
exec usp_Child_proc
select 'Child Proc EXECUTED'
if(@@TRANCOUNT>1)
commit transaction Parent
end try
BEGIN CATCH
ROLLBACK transaction Parent
END CATCH
alter proc usp_Child_proc
as
begin try
begin transaction child
select 'Child Proc'
-- if(@@TRANCOUNT>1)
commit transaction child
end try
BEGIN CATCH
ROLLBACK transaction child
END CATCH
Execution and results:
Exec proc usp_Parent_proc
If there is an error in child procedure
alter proc usp_Parent_proc
as
begin try
begin transaction Parent
select 'Parent Proc Started'
exec usp_Child_proc
select 'Child Proc EXECUTED'
if(@@TRANCOUNT>1)
commit transaction Parent
end try
BEGIN CATCH
ROLLBACK transaction Parent
END CATCH
alter proc usp_Child_proc
as
begin try
begin transaction child
select 'Child Proc'
-- if(@@TRANCOUNT>1)
raiserror('Childproc_error',16,1)
commit transaction child
end try
BEGIN CATCH
ROLLBACK transaction child
END CATCH
Execution and results:
Exec proc usp_Parent_proc
If there is an error in child procedure and no transaction in the child procedure:
create proc usp_Parent_proc
as
begin try
begin transaction
select 'Parent Proc Started'
declare @O_Retvalue int, @O_Retmessage nvarchar(300)
exec usp_Child_proc @O_Retvalue,@O_Retmessage
if( @O_Retvalue= 1)
begin
select 'Child Proc EXECUTED'
commit transaction
end
else
raiserror(@O_Retmessage,16,1)
end try
BEGIN CATCH
select 'Inside Parent Proc CATCH block'
ROLLBACK transaction
END CATCH
go
create proc usp_Child_proc
(@O_Retvalue int, @O_Retmessage nvarchar(300)
)
as
begin try
select 'Child Proc'
-- if(@@TRANCOUNT>1)
raiserror('Child proc_error',16,1)
select @O_Retvalue=1 ,@O_Retmessage='Successful'
end try
BEGIN CATCH
select @O_Retvalue=0 ,@O_Retmessage='Inside Child Proc CATCH block'
END CATCH
Exec usp_Parent_proc
