Click here to Skip to main content
15,885,537 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Error appears when I uncomment Rollback transaction line after OutputID=-1

I want to rollback the transaction if any of the serial number is not matching for a particular OrderID.

Can you please help me where am i going wrong with the transactions. Begin and Commit Statements looks fine for me.Verified all the begin commit statements through out the proc


Below is the error

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


SQL
ALTER PROC dbo.UpdatePartnerSerialNumbersBatchDetails
  @OrderID             INT
, @SerialNumberDetails XML = NULL
, @SerialNumbersCount  INT
, @UploadedBy          VARCHAR(50)
, @UploadedDate        DATETIME
, @OutputID            INT OUT
AS
BEGIN
  
  SET XACT_ABORT, NOCOUNT ON;

  BEGIN TRY
    DECLARE @BatchCount INT;

    IF @SerialNumbersCount <> (SELECT TOP 1
                                      OrderQty
                               FROM   OrderMaster
                               WHERE  OrderID = @OrderID
                                      AND IsDeleted = 0)
    BEGIN
      SET @OutputID = -2;    
    END;
    ELSE
    BEGIN
      BEGIN TRANSACTION SerialNumbers;

      UPDATE
            dbo.PartnerSerialNumbers
      SET
            BatchNo = T.N.value('(BatchNo/text())[1]', 'varchar(50)')
          , DataUploadfromPartner = 1
          , ExpiryDate = T.N.value('(ExpiryDate/text())[1]', 'Datetime')
          , SerialNoStatus = 'O'
          , UploadedBy = @UploadedBy
          , UploadedDate = @UploadedDate
      FROM  @SerialNumberDetails.nodes('Root/SerialNumberDetails') AS T(N)
      WHERE OrderID = @OrderID
            AND SerialNo = T.N.value('(SerialNumber/text())[1]', 'varchar(50)');

      SET @BatchCount = @@ROWCOUNT;

      IF @BatchCount <> (SELECT TOP 1
                                OrderQty
                         FROM   OrderMaster
                         WHERE  OrderID = @OrderID
                                AND IsDeleted = 0)
      BEGIN
       
        SET @OutputID = -1;    
        ROLLBACK TRANSACTION SerialNumbers;
      END;
      ELSE
      BEGIN
        SET @OutputID = 1;

        UPDATE
              OrderMaster
        SET
              DataUploadfromPartner = 1
        WHERE OrderID = @OrderID;

        INSERT INTO SerialnumbersLogDetails
          (
            OrderID
          , SerialNo
          , GTINCode
          , OrderSentDate
          , OrderorPartner
          , BatchNo
          , ExpiryDate
          )
        SELECT
              OrderID
            , SerialNo
            , GTINCode
            , NULL
            , 'Partner'
            , BatchNo
            , ExpiryDate
        FROM  PartnerSerialNumbers
        WHERE OrderID = @OrderID;
      END;

      
      COMMIT TRANSACTION SerialNumbers;
    END;
  END TRY
  BEGIN CATCH    
    SET @OutputID = 0;

    IF @@trancount > 0 ROLLBACK TRANSACTION SerialNumbers;
  END CATCH;
END;


What I have tried:

When i commented the
BEGIN
       
        SET @OutputID = -1;    
       -- ROLLBACK TRANSACTION SerialNumbers;
      END;


It works fine.
Posted
Updated 6-Dec-19 1:56am
v2
Comments
Richard Deeming 6-Dec-19 7:19am    
And the error is?
Prathap Gangireddy 6-Dec-19 7:22am    
Sorry..updated the question
[no name] 6-Dec-19 8:37am    
Should not COMMIT TRANSACTION SerialNumbers; be in the block above? For me it looks like you commit even after rollback.
Prathap Gangireddy 9-Dec-19 2:05am    
This is the usual code i write most of the times..syntax wise everything seems fine..but when i try to send data from front end to stored procedure it throws error. Else i have tried with dummy data in management studio and it return -1 without any issues

1 solution

Although it's not entirely clear from the documentation[^], ROLLBACK TRANSACTION doesn't terminate the stored procedure. The statements after the ROLLBACK continue to execute.

The next statement to execute will be the COMMIT TRANSACTION line, which will fail with an error:
Quote:
Msg 3902, Level 16, State 1, ...
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
However, this should be caught and handled by your CATCH block. But it would be better to put a Return; statement after the ROLLBACK, so that the procedure doesn't try to commit the transaction.

I suspect the code which is calling this stored procedure is also using a transaction, which is causing the CATCH block to try to roll back the SerialNumbers transaction which has already been rolled back. But that still doesn't produce the same error for me.

If adding the Return; statement doesn't fix the problem, perhaps you could show the code which is calling this procedure?
 
Share this answer
 
Comments
Prathap Gangireddy 6-Dec-19 8:12am    
Richard..i have already tried with return statement..still it throws the same error..
Richard Deeming 6-Dec-19 8:17am    
It definitely sounds like a problem with the code that's calling the stored procedure. What happens if you execute the procedure through SQL Server Management Studio, with no extra transactions involved?
Prathap Gangireddy 9-Dec-19 1:54am    
i have run the procedure in the SQL Server Management Studio..its running fine and returning -1 without any errors
Richard Deeming 9-Dec-19 5:48am    
So as I said, it's something to do with the code that's calling the procedure, rather than the procedure itself.
Prathap Gangireddy 9-Dec-19 7:38am    
All other scenarios where OutputID returning -2,1 with the same data working fine. but here its throwing error

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