How to Use While Loop in MySQL Procedure: Syntax and Example

In MySQL stored procedures, use the WHILE loop to repeat a block of statements while a condition is true. The syntax is WHILE condition DO ... END WHILE;. This loop runs repeatedly until the condition becomes false.

Syntax

The WHILE loop in MySQL stored procedures repeats a set of statements as long as the given condition is true.

• WHILE condition DO: Starts the loop and checks the condition.
• statements: The code to execute repeatedly.
• END WHILE;: Ends the loop block.

sql

WHILE condition DO
  -- statements to execute
END WHILE;

Example

This example creates a procedure that uses a WHILE loop to print numbers from 1 to 5 using a variable.

sql

DELIMITER $$
CREATE PROCEDURE print_numbers()
BEGIN
  DECLARE counter INT DEFAULT 1;
  WHILE counter <= 5 DO
    SELECT counter AS Number;
    SET counter = counter + 1;
  END WHILE;
END$$
DELIMITER ;

CALL print_numbers();

Output

Number
1
Number
2
Number
3
Number
4
Number
5

Common Pitfalls

Common mistakes when using WHILE loops in MySQL procedures include:

• Forgetting to update the loop variable inside the loop, causing an infinite loop.
• Not using DELIMITER to change the statement delimiter when creating procedures.
• Using incorrect syntax like missing END WHILE;.

sql

/* Wrong: Infinite loop because counter is not updated */
DELIMITER $$
CREATE PROCEDURE wrong_loop()
BEGIN
  DECLARE counter INT DEFAULT 1;
  WHILE counter <= 3 DO
    SELECT counter;
    -- Missing SET counter = counter + 1;
  END WHILE;
END$$
DELIMITER ;

/* Correct: Updates counter to avoid infinite loop */
DELIMITER $$
CREATE PROCEDURE correct_loop()
BEGIN
  DECLARE counter INT DEFAULT 1;
  WHILE counter <= 3 DO
    SELECT counter;
    SET counter = counter + 1;
  END WHILE;
END$$
DELIMITER ;

Quick Reference

Keyword	Description
WHILE condition DO	Starts the loop and checks the condition
statements	Code executed while condition is true
END WHILE;	Ends the loop block
SET variable = value;	Updates variables inside the loop

Key Takeaways

Use WHILE ... DO ... END WHILE; to repeat statements while a condition is true in MySQL procedures.

Always update the loop variable inside the loop to avoid infinite loops.

Use DELIMITER to define procedures with loops correctly.

Check syntax carefully: missing END WHILE; causes errors.

WHILE loops are useful for repeated tasks inside stored procedures.