WHILE statement in SQL procedures

The WHILE statement defines a set of statements to be executed until a condition that is evaluated at the beginning of the WHILE loop is false.

The while-loop-condition (an expression) is evaluated before each iteration of the loop.

Here is an example of an SQL procedure with a simple WHILE loop:


  CREATE PROCEDURE sum_mn (IN p_start INT
                          ,IN p_end INT
                          ,OUT p_sum INT)
  SPECIFIC sum_mn
  LANGUAGE SQL
  smn: BEGIN

  DECLARE v_temp INTEGER DEFAULT 0;
  DECLARE v_current INTEGER;
 
  SET v_current = p_start;

  WHILE (v_current <= p_end) DO
    SET v_temp = v_temp + v_current;
    SET v_current = v_current + 1;
  END WHILE;
  p_sum = v_current;
  END smn;

Note: Logic such as is shown in this example would be better implemented using a mathematical formula. The simple example serves to demonstrate the syntax.