0

I'm using SQL Server 2008. In my stored procedure I can't rollback transaction on ELSE condition in WHILE.

Error:

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

How to rollback transaction while using cursor?

There are my stored procedure:

BEGIN TRANSACTION
IF @@ERROR=0
BEGIN
   SET NOCOUNT ON

   DECLARE @pjamount float,
           @pjdatej  date,
           @pjaccap varchar(10),
           @acode varchar(13),
           @btot float,
           @cloc varchar(10),
           @dqty int,
           @qtyorder int,
           @qtypurch int

  SELECT 
      @pjdatej = (DATEADD(dd, Supplier_M_Limit, Purchasing_M_Date)), 
      @pjaccap = Supplier_M_Acc 
  FROM 
      tblPurchasing_M 
  JOIN 
      tblPurchOrder_M ON tblPurchasing_M.PurchOrder_M_ID = tblPurchOrder_M.PurchOrder_M_ID 
  JOIN 
      tblSupplier_M ON tblPurchOrder_M.Supplier_M_ID = tblSupplier_M.Supplier_M_ID 
  WHERE 
      Purchasing_M_ID = @pjid

  SET @pjamount = (SELECT SUM(Purchasing_D_Price * Purchasing_D_Qty) 
                   FROM tblPurchasing_D 
                   WHERE Purchasing_M_ID = @pjid)

  INSERT INTO tblRepAPHistory (Depo_M_ID, 
                             RepAPHistory_Trans, 
                             RepAPHistory_Amount, 
                             RepAPHistory_Date, 
                             RepAPHistory_DateJ, 
                             RepAPHistory_DateL, 
                             RepAPHistory_Stats, 
                             SetCategory_ID) 
 VALUES (@depoid, @pjcode, @pjamount, @pjdate, @pjdatej, NULL, 0, @cateid)

INSERT INTO tblRepTransactions (Depo_M_ID, 
                                RepTransactions_Code, 
                                RepTransactions_Acc, 
                                RepTransactions_AmountD, 
                                RepTransactions_AmountK, 
                                RepTransactions_Desc, SetCategory_ID) 
 VALUES (@depoid, @pjcode, @pjaccap, '0', @pjamount, 
         'AP Pembelian '+@pjcode, @cateid)

   IF (CURSOR_STATUS('Global','approvepj'))>0
     BEGIN
        SET NOCOUNT ON
            CLOSE approvepj
            DEALLOCATE approvepj
            END
        DECLARE approvepj CURSOR
        FOR
        SELECT Purchasing_D_Inv AS A, 
               (Purchasing_D_Qty*Purchasing_D_Price) AS B, 
               Purchasing_D_Loc AS C, Purchasing_D_Qty AS D 
        FROM tblPurchasing_D 
        WHERE Purchasing_M_ID = @pjid

        OPEN approvepj
            FETCH NEXT FROM approvepj
            INTO @acode, @btot, @cloc, @dqty

        WHILE @@FETCH_STATUS=0
            BEGIN
                SET NOCOUNT ON
                SET @qtypurch = (SELECT SUM(Purchasing_D_Qty) 
                                 FROM tblPurchasing_D 
                                 WHERE purchasing_M_Id = @pjid 
                                  AND Purchasing_D_Inv=@acode 
                                 GROUP BY Purchasing_D_Inv)
                SET @qtyorder = (SELECT SUM(purchorder_D_qty) 
                                 FROM tblpurchorder_D 
                                 WHERE PurchOrder_M_ID = @pjpo 
                                  AND PurchOrder_D_Inv=@acode 
                                 group by PurchOrder_D_Inv)

                IF @qtypurch <= @qtyorder
                BEGIN
                    INSERT INTO tblRepTransactions (Depo_M_ID, 
                                                    RepTransactions_Code,  
                                                    RepTransactions_Acc, 
                                                    RepTransactions_AmountD, 
                                                    RepTransactions_AmountK, 
                                                    RepTransactions_Desc, 
                                                    SetCategory_ID) 
                      VALUES (@depoid, @pjcode, @pjaccinv, @btot, '0', 
                              'Detil Pembelian: '+@pjcode+' - '+@acode , 
                              @cateid)

                    INSERT INTO tblRepInvHistory (Depo_M_ID, 
                                                  RepInvHistory_Trans, 
                                                  RepInvHistory_Inv, 
                                                  RepInvHistory_Hist, 
                                                  RepInvHistory_Date, 
                                                  RepInvHistory_Loc, 
                                                  SetCategory_ID) 
                     VALUES (@depoid, @pjcode, @acode, @dqty,
                             @pjdate, @cloc, @cateid)

                    IF @@ERROR<>0
                        BEGIN
                            SET NOCOUNT ON
                            ROLLBACK TRANSACTION
                            RETURN
                        END 
                    FETCH NEXT FROM approvepj
                    INTO @acode, @btot, @cloc, @dqty
                END
                ELSE
                    BEGIN
                    SET NOCOUNT ON
                    ROLLBACK TRANSACTION
                END
            END
        CLOSE approvepj
        DEALLOCATE approvepj
    END

ELSE
    BEGIN
    SET NOCOUNT ON
    ROLLBACK TRANSACTION
    END
COMMIT TRANSACTION
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • most likely it is the second time around the rollback did not work. You either need to `BEGIN TRANSACTION` straight after your rollback, or exit the loop. It all depends on the requirements – cha Jul 03 '14 at 04:09
  • @cha oh i got it, thank you. i just remove ROLLBACK TRANSACTION from loop and set variable that used for condition out of the loop – REX SAMSON Jul 03 '14 at 06:34

0 Answers0