CODEWITHSUNDEEP
mysqlstored-procedures
pistacchio
pistacchio

Reputation: 58863

MySQL Cursor / Fetch

I expect the following stored routine to return a series 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.

Upvotes: 1

Views: 6328

Answers (4)

pistacchio
pistacchio

Reputation: 58863

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.

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425331

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?

Upvotes: 2

user121489
user121489

Reputation:

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;

Upvotes: 0

user121489
user121489

Reputation:

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.

Upvotes: 0

Related Questions