WHILE loops in procedures in SQL - Step-by-Step Execution

Concept Flow - WHILE loops in procedures

Start Procedure

↓

Initialize variables

↓

Check WHILE condition

|Yes↓

Execute loop body

↓

Update variables

↓

Back to WHILE condition

|No↓

End Procedure

The procedure starts by initializing variables, then repeatedly checks the WHILE condition. If true, it runs the loop body and updates variables, then checks again until the condition is false.

Execution Sample

SQL

CREATE PROCEDURE CountToFive()
BEGIN
  DECLARE counter INT DEFAULT 1;
  WHILE counter <= 5 DO
    SELECT counter;
    SET counter = counter + 1;
  END WHILE;
END;

This procedure counts from 1 to 5, selecting the current counter value each loop iteration.

Execution Table

Step	counter	Condition (counter <= 5)	Action	Output
1	1	True	SELECT 1; SET counter=2	1
2	2	True	SELECT 2; SET counter=3	2
3	3	True	SELECT 3; SET counter=4	3
4	4	True	SELECT 4; SET counter=5	4
5	5	True	SELECT 5; SET counter=6	5
6	6	False	Exit loop	No output

💡 counter becomes 6, condition 6 <= 5 is False, so loop ends

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	Final
counter	1	2	3	4	5	6	6

Key Moments - 2 Insights

Why does the loop stop after counter reaches 6?

Because the WHILE condition checks if counter <= 5. When counter is 6, the condition is false, so the loop exits as shown in step 6 of the execution_table.

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

The counter would never change, so the condition stays true forever, causing an infinite loop. The execution_table shows the counter increasing each step to avoid this.

Visual Quiz - 3 Questions

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

A 4

B 3

C 2

D 5

💡 Hint

Check the 'counter' column in the execution_table row for Step 3.

At which step does the WHILE condition become false?

A Step 5

B Step 4

C Step 6

D Step 7

💡 Hint

Look at the 'Condition' column in the execution_table to find when it changes to False.

If the initial counter was set to 3, how many times would the loop run?

A 2 times

B 3 times

C 5 times

D 4 times

💡 Hint

Refer to variable_tracker and execution_table logic to count iterations from 3 to 5.

Concept Snapshot

WHILE loops in SQL procedures:
- Syntax: WHILE condition DO ... END WHILE;
- Loop runs while condition is TRUE
- Update variables inside loop to avoid infinite loops
- Exit when condition becomes FALSE
- Useful for repeated actions until a condition changes

Full Transcript

This visual execution shows how a WHILE loop works inside an SQL procedure. The procedure starts by setting a counter to 1. Then it checks if the counter is less than or equal to 5. If yes, it selects the counter value and increases it by 1. This repeats until the counter becomes 6, making the condition false and stopping the loop. The variable tracker shows how the counter changes each step. Key points include the importance of updating the counter to avoid infinite loops and understanding when the loop stops. The quiz questions help reinforce these ideas by asking about counter values and loop termination steps.