MySQL procedure loop error

Question

When running my query I am getting this:

error:

#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 '' at line 3

SQL:

DROP PROCEDURE IF EXISTS insertPromos;
CREATE PROCEDURE insertPromos()
BEGIN
  DECLARE int_val INT DEFAULT 0;
  insertPromosLoop : LOOP

  IF (int_val = 501) THEN
    LEAVE insertPromosLoop;
  END IF;
    INSERT INTO `promo_code` (`code`, `valid_from` ,`valid_to` , `free_period`)VALUES (CONCAT('PROMO', int_val),  '2013-10-10',  '2013-11-10',  'P1M');
    SET int_val = int_val +1;
  END LOOP; 
END;
CALL insertPromos();

juergen d · Accepted Answer · 2013-10-10 10:01:03Z

2

You need to define a new delimiter

delimiter |

DROP PROCEDURE IF EXISTS insertPromos |

CREATE PROCEDURE insertPromos()
BEGIN
  DECLARE int_val INT DEFAULT 0;
  insertPromosLoop : LOOP

  IF (int_val = 501) THEN
    LEAVE insertPromosLoop;
  END IF;
    INSERT INTO `promo_code` (`code`, `valid_from` ,`valid_to` , `free_period`)VALUES (CONCAT('PROMO', int_val),  '2013-10-10',  '2013-11-10',  'P1M');
    SET int_val = int_val +1;
  END LOOP; 
END
|

delimiter ;

CALL insertPromos();

Otherwise the procedure definition would end at the first ; which would not be correct.

answered Oct 10, 2013 at 10:01

juergen d

205k40 gold badges303 silver badges377 bronze badges

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

1 Comment

CharliePrynn Over a year ago

Brilliant, that worked. Thanks a lot. First time using procedures.

Collectives™ on Stack Overflow

MySQL procedure loop error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related