Exit Handler for SQLEexception
Hi folks,
Here is exit handler for my sproc
DECLARE EXIT HANDLER FOR sqlexception
BEGIN
DELETE FROM shopMaster WHERE receiptNo = p_receiptNo;
DELETE FROM shopDetail WHERE receiptNo = p_receiptNo;
ROLLBACK;
SELECT -1;
END;
The problem is even the though the sproc works fine, it always returns -1 as a value. Note, it does not delete from shopMaster and shopDetail. To me it should be returning 1.
Makes no sense to me...
Any ideas ??
Thanks,
J
The full sproc is below...
CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_UpdateTill`(
in p_receiptNo int(11),
IN p_period int(4)
)
BEGIN
DECLARE vMOP varchar(10) default "";
DECLARE vShortMOP varchar(1) default "";
DECLARE vTotal decimal(12,2) default 0;
DECLARE vVatTotal decimal(12,2) default 0;
DECLARE vGoodsTotal decimal(12,2) default 0;
DECLARE vAccountID varchar(4) default "";
DECLARE vAccountTotal decimal(12,2) default 0;
DECLARE vVatCode varchar(1) default "";
DECLARE vVatRate decimal(12,2) default 0;
DECLARE vFinished INT default 0;
DECLARE vFinished2 INT default 0;
DECLARE vCount INT default 0;
DECLARE vStockID varchar(10) default "";
DECLARE vReference varchar(15) default "";
DECLARE vTranType varchar(1) default "";
DECLARE vQuantity decimal(12,2) default 0;
DECLARE vstDept varchar(1) default "";
DECLARE vtotalValue decimal(12,2) default 0;
DECLARE vstUnit decimal(12,2) default 0;
DECLARE vST varchar(1) default "";
DECLARE vAnalysis varchar(1) default "";
DECLARE vaRef varchar(5) default "";
DECLARE vatCursor CURSOR FOR SELECT VC, VR, SUM(quantity * price) as vGoodsTotal, sum(vatAmount) as vvatTotal FROM shopDetail WHERE receiptNo = p_receiptNo GROUP by VC;
DECLARE nominalCursor CURSOR FOR SELECT accountID, SUM(quantity * price) as vAccountTotal FROM shopDetail WHERE receiptNo = p_receiptNo GROUP by accountID;
DECLARE shopDetailCursor CURSOR FOR SELECT itemCode, tranType, stDepartment, total, quantity, st, accountID, analysis, aRef FROM shopDetail WHERE receiptNo = p_receiptNo;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET vfinished = 1;
-- Delete all records from shopmaster and shopdetail with mathcing receiptNo. The records will be left on screen to try again so th erecords have to be deleted.
DECLARE EXIT HANDLER FOR sqlexception
BEGIN
DELETE FROM shopMaster WHERE receiptNo = p_receiptNo;
DELETE FROM shopDetail WHERE receiptNo = p_receiptNo;
ROLLBACK;
SELECT -1;
END;
START TRANSACTION;
-- Get Values from Shop Master
SELECT overallTotal, MOP INTO vTotal, vMOP FROM shopMaster WHERE receiptNo = p_receiptNo;
-- Add Row that stores total of all sales 1st row of till -----------------
SELECT count(*) INTO vCount FROM till WHERE cash = "F";
if vCount = 0 THEN
INSERT INTO TILL (pKey, code, entryDate, ref1, ref2, total, CDW, cash, period, amount, goods, vat, accountID, tran, vc, rate)
VALUES ( 0, "5061", curdate(), "Shop Sales", "", vTotal, "", "F", p_period, 0, 0, 0, "", "T", 0, 0);
else
UPDATE till SET total = total + vTotal WHERE cash = "F" and tran = "T" LIMIT 1;
end if;
-- Vat Transactions -----------------------------------------
OPEN vatCursor;
read_loop: LOOP
FETCH vatCursor INTO vVatCode, vVatRate, vGoodsTotal, vVatTotal;
IF vfinished = 1 THEN
LEAVE read_loop;
end if;
-- Check to see if there is a row with the vat code already there. If not then add one
SELECT count(*) INTO vCount FROM till where vc = vVatCode;
if vCount = 0 THEN
INSERT INTO TILL (pKey, code, entryDate, ref1, ref2, total, CDW, cash, period, amount, goods, vat, accountID, tran, vc , rate)
VALUES ( 0, "", curdate(), "", "", 0, "", "F", 0, 0, 0, 0, "", "F", vVatCode, vVatRate);
end if;
UPDATE till set goods = goods + vGoodsTotal, vat = vat + vVatTotal WHERE vc = vVatCode;
END Loop read_loop;
CLOSE vatCursor;
-- Process MOPS --------------------------------------------
if vMOP = "CASH" THEN SET vShortMOP = "C";
ELSEIF vMOP = "VISA" THEN SET vShortMOP = "V";
ELSEIF vMOP = "CHEQUE" THEN SET vShortMOP = "Q";
ELSEIF vMOP = "AMEX" THEN SET vShortMOP = "S";
END IF;
SELECT COUNT(*) INTO vCount FROM TILL where cash = "T" and CDW = vShortMOP;
if vCount > 0 THEN
UPDATE till SET total = total + vTotal WHERE CDW = vShortMOP and cash = "T";
else
INSERT INTO TILL (pKey, code, entryDate, ref1, ref2, total, CDW, cash, period, amount, goods, vat, accountID, tran, vc, rate)
VALUES ( 0, "", curdate(), "", "",vTotal, vShortMOP, "T", 0, 0, 0, 0, "", "F", 0, 0);
end if;
-- Process Nominal Codes ---------------------------------------------
OPEN nominalCursor;
SET vFinished = 0;
read_loop: LOOP
FETCH nominalCursor INTO vAccountID, vAccountTotal;
IF vfinished = 1 THEN
LEAVE read_loop;
end if;
-- Check to see if there is a row with the vat code already there. If not then add one
SELECT count(*) INTO vCount FROM till where cash = "F" and accountID = vAccountID;
if vCount = 0 THEN
INSERT INTO TILL (pKey, code, entryDate, ref1, ref2, total, CDW, cash, period, amount, goods, vat, accountID, tran, vc, rate)
VALUES ( 0, "", curdate(), "", "", 0, "", "F", 0, vAccountTotal, 0, 0, vAccountID, "F", 0, 0);
else
UPDATE till set amount = amount + vAccountTotal WHERE cash <> "T" and accountID = vAccountID;
end if;
END Loop read_loop;
CLOSE nominalCursor;
-- Process STUPD ADN SAUPD and Stock Tables ------------------------------
OPEN shopDetailCursor;
SET vFinished = 0;
read_loop: LOOP
FETCH shopDetailCursor INTO vStockID, vTranType, vstDept, vTotalValue, vQuantity, vst, vaccountID, vAnalysis, vAref;
IF vfinished = 1 THEN
LEAVE read_loop;
end if;
if vst = "T" THEN
INSERT INTO STUPD (pKey, code, reference, tranType, stDept, totalValue, stUnit, entryDate, st, period)
VALUES ( 0, vStockID, "Shop - " + p_receiptNo, vTranType, vstDept, vTotalValue,vQuantity, curdate(), vst, p_period);
end if;
INSERT INTO SAUPD (pKey, aCode, acc, anUnit, value, period, analysis, aRef)
VALUES( 0, vStockID, vAccountID, vQuantity, vTotalValue, p_period, vAnalysis, vAref);
-- Update Stock
SELECT count(*) INTO vCount from Stock WHERE ID = vStockID;
UPDATE stock SET stock = stock - vQuantity WHERE ID = vStockID;
if vTRanType = "I" OR vTRanType = "V" OR vTRanType = "L" OR vTRanType = "S" THEN
UPDATE STOCK SET pr = pr + vQuantity, yr = yr + vQuantity, lastSaleDate = curdate() WHERE ID = vStockID;
End if;
END Loop read_loop;
CLOSE shopDetailCursor;
SELECT 1;
COMMIT;
END