Variables and control flow in MySQL

Introduction

Variables let you store values to use later. Control flow helps you decide what to do next based on conditions.

You want to store a temporary value during a query or procedure.

You need to run different commands depending on some condition.

You want to repeat actions multiple times with loops.

You want to check if a value meets a condition and act accordingly.

You want to organize complex logic inside stored procedures.

Syntax

MySQL

DECLARE variable_name datatype [DEFAULT value];
SET variable_name = value;

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

WHILE condition DO
  -- statements
END WHILE;

DECLARE is used inside stored programs like procedures or functions.

SET assigns a value to a variable.

IF and WHILE control the flow based on conditions.

Examples

Declare a variable 'count' as an integer and set it to 5.

MySQL

DECLARE count INT DEFAULT 0;
SET count = 5;

Check if 'count' is positive and set 'message' accordingly.

MySQL

IF count > 0 THEN
  SET message = 'Positive';
ELSE
  SET message = 'Zero or Negative';
END IF;

Repeat decreasing 'count' until it reaches zero.

MySQL

WHILE count > 0 DO
  SET count = count - 1;
END WHILE;

Sample Program

This procedure counts down from 3 to 1, sets a message for each number, and shows it.

MySQL

DELIMITER $$
CREATE PROCEDURE demo_variables()
BEGIN
  DECLARE counter INT DEFAULT 3;
  DECLARE result VARCHAR(20);

  WHILE counter > 0 DO
    IF counter = 3 THEN
      SET result = 'Three';
    ELSEIF counter = 2 THEN
      SET result = 'Two';
    ELSE
      SET result = 'One';
    END IF;
    SELECT CONCAT('Count is: ', counter, ', Result is: ', result) AS output;
    SET counter = counter - 1;
  END WHILE;
END$$
DELIMITER ;

CALL demo_variables();

Important Notes

Variables declared with DECLARE only exist inside the procedure or function.

Use DELIMITER to change statement end marker when creating procedures.

Control flow statements must end with END IF, END WHILE, etc.

Summary

Variables store temporary data inside procedures.

Control flow (IF, WHILE) lets you run code based on conditions.

Use DECLARE to create variables and SET to assign values.