Master MySQL Flow Control: IF, CASE, LOOP, WHILE, REPEAT & LEAVE Explained

Flow Control Introduction

Complex problems cannot be solved with a single SQL statement; multiple statements are needed. Flow‑control statements determine the execution order of SQL inside stored procedures, and are essential for implementing sequential, branching, and looping logic.

MySQL provides three categories of flow‑control statements for stored programs: conditional statements (IF, CASE), loop statements (LOOP, WHILE, REPEAT), and jump statements (ITERATE, LEAVE).

Branching with IF

IF expression THEN statement1
[ELSEIF expression2 THEN statement2] ...
[ELSE statementN]
END IF;

Example:

DELIMITER //
CREATE PROCEDURE test_if()
BEGIN
  DECLARE stu_name VARCHAR(15);
  IF stu_name IS NULL THEN
    SELECT 'stu_name is null';
  END IF;
END //
DELIMITER ;

Salary‑adjustment example:

DELIMITER //
CREATE PROCEDURE update_salary_by_eid1(IN emp_id INT)
BEGIN
  DECLARE emp_sal DOUBLE;
  DECLARE hire_year DOUBLE;
  SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
  SELECT DATEDIFF(CURDATE(), hire_date)/365 INTO hire_year FROM employees WHERE employee_id = emp_id;
  IF emp_sal < 8000 AND hire_year >= 5 THEN
    UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id;
  END IF;
END //
DELIMITER ;

Branching with CASE

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  ELSE resultN
END CASE;

Example procedure:

DELIMITER //
CREATE PROCEDURE test_case()
BEGIN
  DECLARE var INT DEFAULT 2;
  CASE var
    WHEN 1 THEN SELECT 'var = 1';
    WHEN 2 THEN SELECT 'var = 2';
    WHEN 3 THEN SELECT 'var = 3';
    ELSE SELECT 'other value';
  END CASE;
END //
DELIMITER ;

Loop with LOOP

[label:] LOOP
  statements
END LOOP [label];

Example that increments a counter until it reaches 10:

DELIMITER //
CREATE PROCEDURE test_loop()
BEGIN
  DECLARE num INT DEFAULT 1;
  loop_label: LOOP
    SET num = num + 1;
    IF num >= 10 THEN LEAVE loop_label; END IF;
  END LOOP loop_label;
  SELECT num;
END //
DELIMITER ;

Loop with WHILE

[label:] WHILE condition DO
  statements
END WHILE [label];

Example that raises salaries by 10% until the average salary reaches 12,000:

DELIMITER //
CREATE PROCEDURE update_salary_loop(OUT num INT)
BEGIN
  DECLARE avg_sal DOUBLE;
  DECLARE loop_count INT DEFAULT 0;
  SELECT AVG(salary) INTO avg_sal FROM employees;
  loop_lab: LOOP
    IF avg_sal >= 12000 THEN LEAVE loop_lab; END IF;
    UPDATE employees SET salary = salary * 1.1;
    SELECT AVG(salary) INTO avg_sal FROM employees;
    SET loop_count = loop_count + 1;
  END LOOP loop_lab;
  SET num = loop_count;
END //
DELIMITER ;

Loop with REPEAT

[label:] REPEAT
  statements
UNTIL condition END REPEAT [label];

Example that increments a counter until it reaches 10 (the body runs at least once):

DELIMITER //
CREATE PROCEDURE test_repeat()
BEGIN
  DECLARE num INT DEFAULT 1;
  REPEAT
    SET num = num + 1;
  UNTIL num >= 10 END REPEAT;
  SELECT num;
END //
DELIMITER ;

Comparing LOOP, WHILE, REPEAT

All three loops can omit a label, but a label is required when using LEAVE or ITERATE inside the loop. LOOP repeats until a LEAVE is executed, WHILE checks the condition before each iteration, and REPEAT checks the condition after each iteration, guaranteeing at least one execution.

Jump Statement LEAVE

LEAVE exits a loop or a BEGIN…END block, similar to break in other languages. LEAVE label; Example:

DELIMITER //
CREATE PROCEDURE leave_begin(IN num INT)
BEGIN
  begin_label: BEGIN
    IF num <= 0 THEN LEAVE begin_label;
    ELSEIF num = 1 THEN SELECT AVG(salary) FROM employees;
    ELSEIF num = 2 THEN SELECT MIN(salary) FROM employees;
    ELSE SELECT MAX(salary) FROM employees;
    END IF;
    SELECT COUNT(*) FROM employees;
  END;
END //
DELIMITER ;

Jump Statement ITERATE

ITERATE restarts the current loop iteration, similar to continue. ITERATE label; Example:

DELIMITER //
CREATE PROCEDURE test_iterate()
BEGIN
  DECLARE num INT DEFAULT 0;
  loop_label: LOOP
    SET num = num + 1;
    IF num < 10 THEN ITERATE loop_label;
    ELSEIF num > 15 THEN LEAVE loop_label;
    END IF;
    SELECT 'Iteration example';
  END LOOP;
END //
DELIMITER ;

Practice Exercises

1. Write a function test_if_case that returns a grade (A‑D) based on a numeric score, using both IF and CASE.

# Using IF
CREATE FUNCTION test_if_case1(score DOUBLE) RETURNS CHAR
BEGIN
  DECLARE ch CHAR;
  IF score > 90 THEN SET ch='A';
  ELSEIF score > 80 THEN SET ch='B';
  ELSEIF score > 60 THEN SET ch='C';
  ELSE SET ch='D';
  END IF;
  RETURN ch;
END //

# Using CASE
CREATE FUNCTION test_if_case2(score DOUBLE) RETURNS CHAR
BEGIN
  DECLARE ch CHAR;
  CASE
    WHEN score > 90 THEN SET ch='A';
    WHEN score > 80 THEN SET ch='B';
    WHEN score > 60 THEN SET ch='C';
    ELSE SET ch='D';
  END CASE;
  RETURN ch;
END //

2. Create a procedure test_if_pro that deletes, updates, or raises salaries based on a given salary threshold.

CREATE PROCEDURE test_if_pro(IN sal DOUBLE)
BEGIN
  IF sal < 3000 THEN
    DELETE FROM employees WHERE salary = sal;
  ELSEIF sal <= 5000 THEN
    UPDATE employees SET salary = salary + 1000 WHERE salary = sal;
  ELSE
    UPDATE employees SET salary = salary + 500 WHERE salary = sal;
  END IF;
END //

3. Write a procedure insert_data that inserts a specified number of rows into the admin table.

CREATE PROCEDURE insert_data(IN insert_count INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= insert_count DO
    INSERT INTO admin(user_name, user_pwd)
    VALUES(CONCAT('Rose-', i), ROUND(RAND()*100000));
    SET i = i + 1;
  END WHILE;
END //