Mysql Cursor / Fetch

Question

Hi, I expect the following Stored Routine to return a serie of rows, while it only returns 1

CREATE PROCEDURE example()
    BEGIN
        DECLARE current_id INT;
        DECLARE done INT DEFAULT 0;
        DECLARE cur_main CURSOR FOR SELECT id from tasks;
        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

        OPEN cur_main;

        FETCH cur_main into current_id;
        lp:WHILE not done DO
         FETCH cur_main into current_id;
         IF done = 1 THEN
          LEAVE lp;
         END IF;
         SELECT * from tasks WHERE id = current_id;
        END WHILE lp;

        CLOSE cur_main;
    END

Any help? This is my very first time with MySQL stored routines.

Answer 1

You might want to try this statement rather than your existing test.

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

The default action for a continue handler is EXIT for an unmatched SQLSTATE value. The "NOT FOUND" constant covers all valid 020 conditions.

Answer 2

Here is another possibility. In looking again at your code I noticed that you do a FETCH before your while loop which will give you the first record. You enter the WHILE loop and then do another FETCH. Typically you would enter the while loop, do your processing with the current record, then do another FETCH right before cycling the loop. In addition, by moving the next FETCH to the end of the loop body you can remove the IF test as well.

FETCH cur_main into current_id;
lp:WHILE not done DO
    SELECT * from tasks WHERE id = current_id;
    FETCH cur_main into current_id;
END WHILE lp;

Answer 3

Unfortunately, MySQL does not return multiple rows this way.

This procedure:

CREATE PROCEDURE example()
BEGIN
    SELECT 1;
    SELECT 2;
END;

will return multiple resultsets, not rows.

Could you please describe what task do you want to achieve with this stored procedure?

Answer 4

Thanks for you replies. I managed to do what I had do with the help of a temporary table where i INSERT all the results and then SELECTing * FROM that table.