Mysql stored procedure error -- LOOP and CURSOR

Question

DECLARE oldID int;
DECLARE DONE boolean DEFAULT FALSE;
DECLARE fineCursor CURSOR FOR SELECT `ifLinkID` FROM `image_fine_link` WHERE `image_id` = pimage_id;
DECLARE coarseCursor CURSOR FOR SELECT `icLinkID` FROM `image_coarse_link` WHERE `image_id` = pimage_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = TRUE;


OPEN fineCursor;
OPEN coarseCursor;

read_loop : LOOP
    oldID = NULL;
    FETCH fineCursor INTO oldID;
    IF oldID IS NOT NULL THEN
    CALL update_others(oldID,"delete","fine color",NULL);
    END IF;

    IF !DONE THEN ITERATE read_loop;
    END IF;
    LEAVE read_loop;
END LOOP;

SET DONE = FALSE;

read_lopp : LOOP
    oldID = NULL;
    FETCH coarseCursor INTO oldID;
    IF oldID IS NOT NULL THEN
    CALL update_others(oldID,"delete","coarse color",NULL);
    END IF;

    IF !DONE THEN ITERATE read_loop; 
    END IF;
    LEAVE read_loop;
END LOOP;

CLOSE fineCursor;
CLOSE coarseCursor;

And the following error appears, really emergent, any idea ?

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= NULL;
    FETCH fineCursor INTO oldID;
    IF oldID IS NOT NULL THEN
    CALL upd' at line 16

Thank you so much sincerely !~

peterm · Accepted Answer · 2013-09-03 06:09:02Z

1

That particular error is caused by fact that you didn't use SET statement to assign a value to a variable.

Change

oldID = NULL;

to

SET oldID = NULL;

answered Sep 3, 2013 at 6:09

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

peterm Over a year ago

@user2741837 You're welcome :) If it was what you were looking for please, consider to accept the answer.

user2741837 Over a year ago

Hi indeed there is another problem with this code: the error reports: #1308 - ITERATE with no matching label: read_loop, I guess it's because the scope of label is not visible to the loop, what am I supposed to do instead, as I always need a judgement condition to leave or iterate the loop ? :) Appreciate it if you can help

peterm Over a year ago

You have a typo read_lopp : LOOP. lopp instead of loop. BTW you should use two different labels e.g. (read_loop and read_loop2)

user2741837 Over a year ago

Thanks so much, I'm quite a newbie in stackoverflow. This is the first question I posted :)

Collectives™ on Stack Overflow

Mysql stored procedure error -- LOOP and CURSOR

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related