Reputation: 24825
With mysql-5.7.30 it seems that the following loop syntax is invalid.
mysql> SET @r = 6
-> SET @i = 3
-> WHILE @i <= @r DO
-> BEGIN
-> PRINT @i
-> END WHILE;
ERROR 1064 (42000): 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 'SET @i = 3
WHILE @i <= @r DO
BEGIN
PRINT @i
END WHILE' at line 2
mysql>
I see various formats, e.g. WHILE i <= r DO
and it seems that different versions, have minor syntax differences. How can I fix that?
UPDATE:
I even created a procedure according to the answer as below but as you can see it fails.
mysql> CREATE PROCEDURE dowhile()
-> BEGIN
-> DECLARE r INT DEFAULT 4
-> DECLARE i INT DEFAULT 3
-> WHILE i <= r DO
-> SELECT * FROM mdl_user WHERE id=i
-> END WHILE
-> END;
ERROR 1064 (42000): 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 'DECLARE i INT DEFAULT 3
WHILE i <= r DO
SELECT * FROM mdl_user WHERE id=i
mysql>
UPDATE:
With the example about delimiter, I wrote the following code but got an error at the end.
mysql> DELIMITER //
mysql> CREATE PROCEDURE dowhile()
-> BEGIN
-> DECLARE r INT DEFAULT 4;
-> DECLARE i INT DEFAULT 3;
-> WHILE i <= r DO
-> SELECT * FROM mdl_user WHERE id=i;
-> END WHILE;
-> END //
ERROR 1044 (42000): Access denied for user 'moodleuser'@'localhost' to database 'moodle'
UPDATE:
The following code is also wrong.
$ mysql -u moodleuser -p moodle
Enter password:
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE PROCEDURE dowhile()
-> BEGIN
-> DECLARE r INT DEFAULT 4;
ERROR 1064 (42000): 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
Upvotes: 0
Views: 2674
Reputation: 222722
You are missing semi-colons at the end of each statement. Also, you need to increment i
in between each iteration (otherwise it's a infinite loop).
Consider:
DELIMITER //
CREATE PROCEDURE dowhile()
BEGIN
DECLARE r INT DEFAULT 4;
DECLARE i INT DEFAULT 3;
WHILE i <= r DO
SELECT * FROM mdl_user WHERE id = i;
SET i = i + 1;
END WHILE;
END//
DELIMITER ;
Upvotes: 1