Click here to Skip to main content
15,887,822 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,
I have a stored procedure which also calls many nested procedures.
Recently I have added transaction and I am getting below message

'
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
'

Can you please suggest a possible suggestion?
Many Thanks

My procedure structure is like below

CREATE	Procedure [dbo].[My_Main_PROC]  
    @RequestId UNIQUEIDENTIFIER,
    @DOCUMENT XML
AS


*/
BEGIN
SET NOCOUNT ON;

DECLARE @transtate BIT
IF @@TRANCOUNT = 0
BEGIN
	SET @transtate = 1
	BEGIN TRANSACTION transtate
END

BEGIN TRY

......
......

EXEC [dbo].[PROC_FirstNestedPRoc]  ;

......
......

EXEC [dbo].[PROC_SecondNEstedProc]  ;


......
......

EXEC [dbo].[PROC_ThirdNestedPRoc] ;

....
.....

IF @transtate = 1 
        AND XACT_STATE() = 1
        COMMIT TRANSACTION transtate
END TRY
BEGIN CATCH

DECLARE @Error_Message VARCHAR(5000)
DECLARE @Error_Severity INT
DECLARE @Error_State INT

SELECT @Error_Message = ERROR_MESSAGE()
SELECT @Error_Severity = ERROR_SEVERITY()
SELECT @Error_State = ERROR_STATE()

   IF @transtate = 1 
   AND XACT_STATE() <> 0
   ROLLBACK TRANSACTION

RAISERROR (@Error_Message, @Error_Severity, @Error_State)

END CATCH
END


What I have tried:

I tried removing the transaction it works, but when I applied transaction same error happens
Posted
Comments
ZurdoDev 15-Nov-19 15:41pm    
You probably have a commit tran inside one of the other stored procs.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900