Code: Select all
PROCEDURE ambs.BadNFromCDRtoDST_CODES_wodate(IN MaxCallsCount TINYINT(4) UNSIGNED, IN MinACD TINYINT(4) UNSIGNED, OUT BadNoCount INT)
BEGIN
DECLARE VPhone VARCHAR (255);
DECLARE VCallsCount INT (11) UNSIGNED;
DECLARE VACD INT (11) UNSIGNED;
DECLARE done INT DEFAULT 0;
DECLARE curcat CURSOR FOR SELECT
DST_NUMBER_BILL, COUNT(DST_NUMBER_BILL) AS CALLSCOUNT,
(SUM(ELAPSED_TIME) DIV COUNT(DST_NUMBER_BILL)) AS ACD
FROM CDR
GROUP BY DST_NUMBER_BILL
ORDER BY CALLSCOUNT DESC, ACD ASC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET BadNoCount = BadNoCount - 1;
SET BadNoCount = 0;
OPEN curcat;
wet:
LOOP
IF done THEN LEAVE wet;
END IF;
FETCH curcat INTO VPhone, VCallsCount, VACD;
IF VPhone '' THEN
IF VCallsCount >= MaxCallsCount AND VACD <= MinACD THEN
BEGIN
INSERT INTO DEST_CODE VALUES (7, VPhone);
SET BadNoCount=BadNoCount+1;
END;
END IF;
END IF;
END LOOP;
CLOSE curcat;
ENDCode: Select all
with DataModule2 do begin MyBadNFromCDRtoDST_CODES_wodate.ParamByName('MaxCallsCount').AsInteger:=ElSpinEdit1.Value; MyBadNFromCDRtoDST_CODES_wodate.ParamByName('MinACD').AsInteger:=ElSpinEdit2.Value;
MyBadNFromCDRtoDST_CODES_wodate.Execute;
MessageDlg(MyBadNFromCDRtoDST_CODES_wodate.ParamByName('BadNoCount').AsString, mtInformation,[mbOk],0);
end;
But MyBadNFromCDRtoDST_CODES_wodate executed in dbForge for MySQL without any problems and always returned correct BadCountNo value.
MySQL version is 5.0.85-community-nt
I have no idea what's wrong with my code. Please help!