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