Control Flow Statements Inside Procedures

MySQL stored procedures support several control flow statements, enabling logic and repetition similar to traditional programming languages. These constructs allow developers to manage decision-making and loops within the procedure body effectively.

1. IF…THEN…ELSE…END IF 

Executes conditional logic based on a boolean expression. Useful for simple decision-making within procedures.

Example:

CREATE PROCEDURE CheckAge(IN age INT)
BEGIN
  IF age >= 18 THEN
    SELECT 'Adult';
  ELSE
    SELECT 'Minor';
  END IF;
END;  

2. CASE…WHEN…THEN…ELSE…END CASE

Handles multi-way branching based on matching conditions. Ideal for replacing nested IF statements.

Example:

CREATE PROCEDURE GradeCategory(IN score INT)
BEGIN
  CASE
    WHEN score >= 90 THEN SELECT 'Excellent';
    WHEN score >= 75 THEN SELECT 'Good';
    WHEN score >= 50 THEN SELECT 'Average';
    ELSE SELECT 'Fail';
  END CASE;
END;  

3. WHILE…DO…END WHILE

Repeats a block of code while a condition remains true. Suitable for loops with pre-checked conditions.

Example:

CREATE PROCEDURE CountDown(IN start INT)
BEGIN
  WHILE start > 0 DO
    SELECT start;
    SET start = start - 1;
  END WHILE;
END;  

4. REPEAT…UNTIL…END REPEAT

Executes the block at least once, then repeats until a condition becomes true. Useful for post-condition loops.

Example:

CREATE PROCEDURE RepeatMessage(IN count INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  REPEAT
    SELECT CONCAT('Message ', i);
    SET i = i + 1;
  UNTIL i >= count
  END REPEAT;
END;  

5. LOOP…LEAVE…END LOOP

Creates an indefinite loop that must be exited manually using LEAVE. Flexible for complex looping scenarios.

Example:

CREATE PROCEDURE LoopExample()
BEGIN
  DECLARE i INT DEFAULT 1;
  loop_label: LOOP
    IF i > 5 THEN
      LEAVE loop_label;
    END IF;
    SELECT CONCAT('Iteration ', i);
    SET i = i + 1;
  END LOOP;
END;