Variables and control flow in MySQL - Step-by-Step Execution

Concept Flow - Variables and control flow

Start

↓

Declare Variables

↓

Assign Initial Values

↓

Evaluate Condition

|Yes↓

Execute Statements

↓

Update Variables

↓

Loop Back to Condition

No↓

End

This flow shows how variables are declared, assigned, checked in conditions, updated, and how control loops until the condition is false.

Execution Sample

MySQL

SET @counter = 1;
WHILE @counter <= 3 DO
  SELECT @counter;
  SET @counter = @counter + 1;
END WHILE;

This code uses a variable to count from 1 to 3, selecting the value each time.

Execution Table

Step	Variable @counter	Condition (@counter <= 3)	Action	Output
1	1	1 <= 3 is TRUE	SELECT 1; SET @counter = 2	1
2	2	2 <= 3 is TRUE	SELECT 2; SET @counter = 3	2
3	3	3 <= 3 is TRUE	SELECT 3; SET @counter = 4	3
4	4	4 <= 3 is FALSE	Exit loop	No output

💡 Loop ends because @counter is 4 and condition 4 <= 3 is FALSE

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
@counter	undefined	1	2	3	4

Key Moments - 2 Insights

Why does the loop stop when @counter is 4 even though we set it to 4 inside the loop?

Because the condition is checked before each loop iteration (see step 4 in execution_table). When @counter becomes 4, the condition @counter <= 3 is FALSE, so the loop exits before running the body again.

What happens if we forget to update @counter inside the loop?

The condition will always be TRUE (e.g., @counter stays 1), causing an infinite loop. The execution_table shows how @counter changes each step to avoid this.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of @counter at Step 3?

A 2

B 3

C 4

D 1

💡 Hint

Check the 'Variable @counter' column at Step 3 in the execution_table.

At which step does the loop condition become false?

A Step 4

B Step 3

C Step 2

D Step 1

💡 Hint

Look at the 'Condition' column in the execution_table to find when it is FALSE.

If we change the initial value of @counter to 4, what will happen?

A The loop will run once

B The loop will run three times

C The loop will not run at all

D The loop will run infinitely

💡 Hint

Refer to the condition check in the execution_table and consider if 4 <= 3 is TRUE or FALSE at the start.

Concept Snapshot

Variables and control flow in MySQL:
- Declare variables with SET @var = value;
- Use WHILE loops to repeat while a condition is TRUE
- Condition checked before each loop iteration
- Update variables inside loop to avoid infinite loops
- Loop ends when condition becomes FALSE

Full Transcript

This lesson shows how to use variables and control flow in MySQL. We start by declaring a variable @counter and setting it to 1. Then we use a WHILE loop that runs as long as @counter is less than or equal to 3. Inside the loop, we select the current value of @counter and then increase it by 1. The loop repeats until @counter becomes 4, at which point the condition is false and the loop stops. We track the variable changes and see how the condition controls the loop. This helps avoid infinite loops by updating variables properly.

Practice

1. What is the purpose of a variable in MySQL stored procedures?

easy

A. To create new tables dynamically

B. To permanently save data in the database

C. To store temporary data during procedure execution

D. To define user permissions

Solution

1. Step 1: Understand variable role in procedures

   Variables hold temporary data only while the procedure runs.

2. Step 2: Differentiate from permanent storage

   Permanent data is stored in tables, not variables.

3. Final Answer:

   To store temporary data during procedure execution -> Option C

4. Quick Check:

   Variables = Temporary data [OK]

Hint: Variables hold temporary data inside procedures [OK]

Common Mistakes:

• Confusing variables with table data storage
• Thinking variables create database objects
• Assuming variables control user access

2. Which of the following is the correct way to declare and set a variable in MySQL stored procedures?

easy

A. SET x INT; DECLARE x = 10;

B. DECLARE x INT; SET x = 10;

C. DECLARE x = 10 INT;

D. SET x = 10; DECLARE x INT;

Solution

1. Step 1: Recall syntax for variable declaration

   Use DECLARE variable_name data_type; to declare.

2. Step 2: Assign value using SET

   Use SET variable_name = value; after declaration.

3. Final Answer:

   DECLARE x INT; SET x = 10; -> Option B

4. Quick Check:

   DECLARE then SET = correct syntax [OK]

Hint: Declare first, then set value with SET [OK]

Common Mistakes:

• Using SET before DECLARE
• Combining declaration and assignment incorrectly
• Wrong order of keywords

3. Consider the following MySQL procedure snippet:

DECLARE counter INT DEFAULT 1;
WHILE counter <= 3 DO
  SET counter = counter + 1;
END WHILE;
SELECT counter;

What will be the output of the SELECT counter; statement?

medium

A. 3

B. Error due to missing BEGIN...END

C. 1

D. 4

Solution

1. Step 1: Trace the WHILE loop increments

   Counter starts at 1, increments by 1 each loop until counter > 3.

2. Step 2: Count loop iterations and final value

   Loop runs while counter=1,2,3; increments to 4 and stops.

3. Final Answer:

   4 -> Option D

4. Quick Check:

   Counter ends at 4 after loop [OK]

Hint: WHILE increments counter until condition false [OK]

Common Mistakes:

• Assuming counter stops at 3
• Forgetting counter increments before condition check
• Thinking syntax error without BEGIN...END in snippet

4. Identify the error in this MySQL stored procedure snippet:

DECLARE total INT;
SET total = 0
WHILE total < 5 DO
  SET total = total + 1;
END WHILE;

medium

A. Missing semicolon after SET total = 0

B. WHILE loop condition syntax is wrong

C. DECLARE must be inside BEGIN...END block

D. Variable total not declared

Solution

1. Step 1: Check statement endings

   SET total = 0 is missing a semicolon at the end.

2. Step 2: Validate syntax for DECLARE and WHILE

   DECLARE is valid before BEGIN, and WHILE syntax is correct.

3. Final Answer:

   Missing semicolon after SET total = 0 -> Option A

4. Quick Check:

   Every statement needs semicolon [OK]

Hint: Check semicolons after each statement [OK]

Common Mistakes:

• Ignoring missing semicolon errors
• Thinking DECLARE must be inside BEGIN...END always
• Misreading WHILE syntax

5. You want to create a MySQL stored procedure that counts how many numbers between 1 and 10 are even, using variables and control flow. Which of the following code snippets correctly implements this?

hard

A. DECLARE count_even INT DEFAULT 0; DECLARE i INT DEFAULT 1; WHILE i <= 10 DO IF i % 2 = 0 THEN SET count_even = count_even + 1; END IF; SET i = i + 1; END WHILE; SELECT count_even;

B. DECLARE count_even INT; SET count_even = 0; FOR i IN 1..10 DO IF i % 2 = 0 THEN SET count_even = count_even + 1; END IF; END FOR; SELECT count_even;

C. DECLARE count_even INT DEFAULT 0; SET i = 1; WHILE i < 10 DO IF i % 2 = 1 THEN SET count_even = count_even + 1; END IF; SET i = i + 1; END WHILE; SELECT count_even;

D. DECLARE count_even INT DEFAULT 0; DECLARE i INT DEFAULT 1; WHILE i <= 10 DO IF i / 2 = 0 THEN SET count_even = count_even + 1; END IF; SET i = i + 1; END WHILE; SELECT count_even;

Solution

1. Step 1: Check loop and variable initialization

   DECLARE count_even INT DEFAULT 0; DECLARE i INT DEFAULT 1; WHILE i <= 10 DO IF i % 2 = 0 THEN SET count_even = count_even + 1; END IF; SET i = i + 1; END WHILE; SELECT count_even; correctly declares variables and initializes them.

2. Step 2: Verify condition and increment logic

   DECLARE count_even INT DEFAULT 0; DECLARE i INT DEFAULT 1; WHILE i <= 10 DO IF i % 2 = 0 THEN SET count_even = count_even + 1; END IF; SET i = i + 1; END WHILE; SELECT count_even; uses WHILE loop from 1 to 10, checks even with modulo, increments count and i properly.

3. Step 3: Identify errors in other options

   DECLARE count_even INT; SET count_even = 0; FOR i IN 1..10 DO IF i % 2 = 0 THEN SET count_even = count_even + 1; END IF; END FOR; SELECT count_even; uses invalid FOR syntax; C counts odd numbers; D uses division instead of modulo.

4. Final Answer:

   Option A code correctly counts even numbers between 1 and 10 -> Option A

5. Quick Check:

   Use WHILE + modulo for even check [OK]

Hint: Use WHILE loop and modulo (%) to check even numbers [OK]

Common Mistakes:

• Using invalid FOR loop syntax in MySQL
• Confusing modulo (%) with division (/)
• Counting odd numbers instead of even