IF & LOOP in Procedures

IF and LOOP inside a procedure are like the choices and repeats in a chore list: if this is true do that, and keep doing something until you are done.

In this page:

Using IF-THEN in Procedures
Adding ELSEIF for Multiple Choices
The Simple LOOP Statement
The WHILE Loop
The REPEAT Loop

Syntax

sql

IF condition THEN
  -- statements
ELSEIF condition THEN
  -- statements
ELSE
  -- statements
END IF;

loop_label: LOOP
  -- statements
  IF exit_condition THEN LEAVE loop_label; END IF;
END LOOP;

Using IF-THEN in Procedures

IF-THEN inside a stored procedure works like conditional logic in any programming language, running a block of statements only when a specified condition evaluates to true.

Example: Using IF-THEN in Procedures

sql

DELIMITER //
CREATE PROCEDURE CheckStock(IN qty INT)
BEGIN
  IF qty < 10 THEN
    SELECT 'Low stock' AS message;
  END IF;
END //
DELIMITER ;
CALL CheckStock(5);

Adding ELSEIF for Multiple Choices

ELSEIF chains additional conditions onto an IF block, letting the procedure check several possibilities in sequence and run different logic depending on which one matches first.

Example: Adding ELSEIF for Multiple Choices

sql

DELIMITER //
CREATE PROCEDURE GradeScore(IN score INT)
BEGIN
  IF score >= 90 THEN
    SELECT 'A' AS grade;
  ELSEIF score >= 70 THEN
    SELECT 'B' AS grade;
  ELSE
    SELECT 'F' AS grade;
  END IF;
END //
DELIMITER ;
CALL GradeScore(75);

The Simple LOOP Statement

A plain LOOP repeats its contents indefinitely until you explicitly break out of it with a LEAVE statement — forgetting that statement is the classic way to accidentally write an infinite loop inside a procedure.

Example: The Simple LOOP Statement

sql

DELIMITER //
CREATE PROCEDURE CountToFive()
BEGIN
  DECLARE i INT DEFAULT 1;
  my_loop: LOOP
    IF i > 5 THEN
      LEAVE my_loop;
    END IF;
    SET i = i + 1;
  END LOOP;
  SELECT i AS final_value;
END //
DELIMITER ;
CALL CountToFive();

The WHILE Loop

WHILE checks its condition before each pass through the loop body, so if the condition starts out false, the loop body never runs at all, unlike some loop types that always execute at least once.

Example: The WHILE Loop

sql

DELIMITER //
CREATE PROCEDURE CountToFiveWhile()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= 5 DO
    SET i = i + 1;
  END WHILE;
  SELECT i AS final_value;
END //
DELIMITER ;
CALL CountToFiveWhile();

The REPEAT Loop

REPEAT is the opposite of WHILE: it runs its body first and only checks the condition afterward, guaranteeing the code inside executes at least one time before the loop can exit.

Example: The REPEAT Loop

sql

DELIMITER //
CREATE PROCEDURE CountToFiveRepeat()
BEGIN
  DECLARE i INT DEFAULT 1;
  REPEAT
    SET i = i + 1;
  UNTIL i > 5 END REPEAT;
  SELECT i AS final_value;
END //
DELIMITER ;
CALL CountToFiveRepeat();

Chapter Quiz — Complete all 5 topics to unlock

0/5 topics done

Complete these topics first:

← Previous Lesson User-Defined Functions Next Lesson → CURSOR in Procedures