Variables and Control Flow in Procedures

Inside a stored procedure, you can declare local variables, use conditional logic with IF/ELSEIF/ELSE, and iterate with LOOP, WHILE, or REPEAT. This makes procedures capable of implementing complex server-side logic.

Declaring Variables

Use DECLARE to define a local variable with a data type and optional default value. Variables are scoped to the BEGIN...END block in which they are declared.

Control Flow

IF ... ELSEIF ... ELSE ... END IF — conditional branching
CASE ... WHEN ... END CASE — multi-way branching
WHILE condition DO ... END WHILE — pre-condition loop
REPEAT ... UNTIL condition END REPEAT — post-condition loop
LOOP ... END LOOP with LEAVE — infinite loop with explicit exit

Example

DELIMITER //

CREATE PROCEDURE ApplyDiscount(IN order_id INT)
BEGIN
    DECLARE v_total DECIMAL(10,2);
    DECLARE v_discount DECIMAL(5,2) DEFAULT 0.00;

    SELECT total INTO v_total FROM orders WHERE id = order_id;

    IF v_total > 500 THEN
        SET v_discount = 0.15;
    ELSEIF v_total > 200 THEN
        SET v_discount = 0.10;
    ELSE
        SET v_discount = 0.05;
    END IF;

    UPDATE orders
    SET discount = v_discount
    WHERE id = order_id;
END //

DELIMITER ;

SQL

Full Editor

Pro Tip

Declare all variables at the top of the BEGIN block before any executable statements.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned