user2741837
user2741837

Reputation: 45

Mysql stored procedure error -- LOOP and CURSOR

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 !~

Upvotes: 0

Views: 2463

Answers (1)

peterm
peterm

Reputation: 92845

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;

Upvotes: 1

Related Questions