Marc Bourque Marc Bourque - 3 months ago 9
MySQL Question

How to make a while in a stored procedure with phpmyadmin, error #1064

I try to create a stored procedure in phpmyadmin, but I get a error 1064 on line 12 (where the WHILE is). This is the first time I try to create a stored procedure.

BEGIN

DECLARE product_id INT;
DECLARE product_min_age nvarchar(500);
DECLARE cur CURSOR for
SELECT product_min_age, product_id FROM _vm_product;

open cur;

fetch next from cur into product_min_age, product_id;

while FETCH_STATUS = 0 BEGIN

INSERT INTO _virtuemart_product_customfields (virtuemart_product_id, virtuemart_custom_id, customfield_value, customfield_params) VALUES
( product_id, 5, product_min_age, 'addEmpty=0|selectType=0|');

fetch next from cur into product_min_age,product_id;
END;

close cur;

END


Thank you

Answer

You should change that to below. See Documentation for more information.

open cur;

read_loop: LOOP
fetch cur into product_min_age, product_id;

    INSERT INTO _virtuemart_product_customfields (virtuemart_product_id, virtuemart_custom_id, customfield_value, customfield_params) VALUES
    ( product_id, 5, product_min_age, 'addEmpty=0|selectType=0|');

END LOOP;

close cur;
Comments