CODEWITHSUNDEEP
mysqlsqlloopsstored-procedures
mahmood
mahmood

Reputation: 24825

Correct syntax for writing WHILE loop in MYSQL

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

Answers (2)

GMB
GMB

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

slaakso
slaakso

Reputation: 9080

Flow control statements like WHILE can be used in stored programs. Also BEGIN in WHILE and PRINT are used in SQL Server / Sybase, not in MySQL.

See documentation for WHILE

Upvotes: 0

Related Questions