3

I am trying to use a WHILE loop in MySQL v5.7 and keep getting a syntax error. I haven't been able to identify the problem. The syntax looks correct according to the documentation.

I found a thread here suggesting wrapping the statement in a DELIMITER, but this did not work either. The code is:

SET @counter = 1;

WHILE (@counter < 2) DO
    SELECT @counter;
    @counter = @counter + 1;
END WHILE

and the error message is:

ERROR 1064 (42000) at line 22: 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 'WHILE (@counter < 2) DO SELECT @counter' at line 1

What am I missing?

Nisarg Shah
  • 14,151
  • 6
  • 34
  • 55
Jonathan
  • 65
  • 6
  • How did you use the DELIMITER show us. – Jorge Campos Sep 16 '17 at 02:54
  • Yes, I tried with and without a semi-colon after the `END WHILE`. When using the delimiter, I put `DELIMITER //` before the above block and `//` after (with and without the semi-colon). – Jonathan Sep 16 '17 at 07:37

2 Answers2

5

As far as I remember, you cannot use WHILE LOOP just like that. You have to put it inside a Stored Procedure like so:

CREATE PROCEDURE mysp()
BEGIN
    DECLARE counter int DEFAULT 1;
    WHILE counter < 2 DO
        SET counter = counter + 1;
    END WHILE;
END
Ruben_PH
  • 1,692
  • 7
  • 25
  • 42
  • Don't forget `DELIMITER` – tadman Sep 16 '17 at 04:02
  • This is the closest so far. The above code, with the DELIMITER, gives me a different error: ERROR 1044 (42000) at line 18: Access denied for user 'run_OT1FwXgLf31'@'localhost' to database 'run_ot1fwxglf31' Looks like I can't create a procedure in this environment :-( – Jonathan Sep 16 '17 at 07:40
4

Try the following code. It ran successfully on my MySQL 5.7

DELIMITER //

CREATE PROCEDURE mysp()
BEGIN
   DECLARE counter INT;
   SET counter = 1;

   label1: WHILE counter < 2 DO
     SELECT counter;
     SET counter = counter + 1;
   END WHILE label1;
END; //

DELIMITER ;
Ferdinand Gaspar
  • 2,043
  • 1
  • 8
  • 17
  • Your sample procedure is exemplary. Two thing I found missing in my simple procedure after reading your script: 1) The ; after last END, event with different DELIMITER declared. 2) The label `label1` for the `while` loop However I can safely remove the label if I don't want to and I can still successfully create my procedure. Many thanks!!! – Hoang Duc Nguyen Mar 02 '19 at 08:19