Hi Guys,
I'm writing a simple stored procedure, having a cursor within. I have this problem..it seems that the cursor is only retrieving 1 row (the first one ) and keeps on using it for the rest of the process! is there any reason. below find the process in a nutshell...cheers
Any ideas pls?
regards
NF
I'm writing a simple stored procedure, having a cursor within. I have this problem..it seems that the cursor is only retrieving 1 row (the first one ) and keeps on using it for the rest of the process! is there any reason. below find the process in a nutshell...cheers
Code:
CREATE PROCEDURE `sp_AStatisticsReport`(
IN startDate DATE,
IN endDate DATE
)
BEGIN
-- Current AID for Cursor --
DECLARE aId INT;
-- A Cursor counter --
DECLARE done INT DEFAULT 0;
-- Cursor for A --
DECLARE aCursor CURSOR FOR (SELECT AID FROM A WHERE STATUS = 2 ORDER BY AID DESC);
-- Continue Handler for Cursor --
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- Open Cursor --
OPEN aCursor;
-- Start Cursor Readings --
REPEAT
FETCH aCursor INTO aId;
IF NOT done THEN
INSERT INTO `statsReport` (aId)
VALUES (aId);
END IF;
UNTIL done END REPEAT;
-- Close Cursor --
CLOSE aCursor;
-- Select Required Results --
SELECT aId,
FROM `statsReport`;
END$$
Any ideas pls?
regards
NF