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.

Practice

1. What is the main purpose of a WHILE loop inside a SQL procedure?

easy

A. To repeat a set of SQL statements while a condition is true

B. To create a new table in the database

C. To delete rows from a table

D. To join two tables together

Solution

1. Step 1: Understand the role of WHILE loops

   A WHILE loop runs repeatedly as long as its condition remains true.

2. Step 2: Identify the purpose in procedures

   Inside procedures, WHILE loops automate repeated tasks by running code multiple times.

3. Final Answer:

   To repeat a set of SQL statements while a condition is true -> Option A

4. Quick Check:

   WHILE loops repeat code while condition true = D [OK]

Hint: WHILE loops run code repeatedly while condition is true [OK]

Common Mistakes:

• Confusing WHILE with creating or deleting tables
• Thinking WHILE joins tables
• Assuming WHILE runs only once

2. Which of the following is the correct syntax to start a WHILE loop in a SQL procedure?

easy

A. WHILE condition DO

B. WHILE condition BEGIN

C. WHILE (condition BEGIN

D. WHILE condition LOOP

Solution

1. Step 1: Recall standard SQL procedure syntax

   In many SQL dialects, a WHILE loop starts with WHILE condition BEGIN to mark the loop start.

2. Step 2: Compare options

   WHILE condition BEGIN matches the correct syntax without extra parentheses or keywords like DO or LOOP.

3. Final Answer:

   WHILE condition BEGIN -> Option B

4. Quick Check:

   WHILE condition BEGIN is correct syntax [OK]

Hint: WHILE loops use BEGIN to start block, no DO or LOOP [OK]

Common Mistakes:

• Adding DO after WHILE (not standard in SQL)
• Using LOOP keyword (not in standard SQL)
• Putting condition in parentheses unnecessarily

3. Consider this SQL procedure snippet:

DECLARE @counter INT = 1;
WHILE @counter <= 3
BEGIN
  PRINT @counter;
  SET @counter = @counter + 1;
END

What will be printed when this runs?

medium

A. 1 2 3

B. 1 2 3 4

C. 0 1 2

D. No output, syntax error

Solution

1. Step 1: Analyze loop initialization and condition

   The counter starts at 1 and loops while it is less than or equal to 3.

2. Step 2: Trace loop iterations

   It prints 1, then increments to 2, prints 2, increments to 3, prints 3, then increments to 4 and stops.

3. Final Answer:

   1 2 3 -> Option A

4. Quick Check:

   Loop prints 1 to 3 inclusive = A [OK]

Hint: Count from start to condition limit inclusive [OK]

Common Mistakes:

• Thinking loop prints 4 because of last increment
• Confusing starting value as 0
• Assuming syntax error due to PRINT

4. Identify the error in this SQL procedure snippet:

DECLARE @i INT = 1;
WHILE @i < 5
BEGIN
  PRINT @i;
END

medium

A. Incorrect declaration of @i variable

B. PRINT statement syntax is wrong

C. Missing increment of @i inside the loop causing infinite loop

D. WHILE condition should use <= instead of <

Solution

1. Step 1: Check loop body for variable update

   The variable @i is never changed inside the loop, so condition stays true forever.

2. Step 2: Understand effect of missing increment

   Without increment, loop runs infinitely printing @i repeatedly.

3. Final Answer:

   Missing increment of @i inside the loop causing infinite loop -> Option C

4. Quick Check:

   Loop variable must update to avoid infinite loop = B [OK]

Hint: Always update loop variable inside WHILE to avoid infinite loop [OK]

Common Mistakes:

• Thinking PRINT syntax is wrong
• Believing declaration is incorrect
• Assuming condition operator causes error

5. You want to create a procedure that sums numbers from 1 to N using a WHILE loop. Which of these code snippets correctly implements this?

hard

A. DECLARE @sum INT = 0;WHILE @sum <= @NBEGIN SET @sum = @sum + 1;END

B. DECLARE @sum INT = 0, @i INT = 1;WHILE @i < @NBEGIN SET @sum = @sum + @i;END

C. DECLARE @sum INT = 0, @i INT = 1;WHILE @i <= @NBEGIN SET @sum = @sum + @i; SET @i = @i - 1;END

D. DECLARE @sum INT = 0, @i INT = 1;WHILE @i <= @NBEGIN SET @sum = @sum + @i; SET @i = @i + 1;END

Solution

1. Step 1: Check loop condition and variable updates

   DECLARE @sum INT = 0, @i INT = 1;
   WHILE @i <= @N
   BEGIN
   SET @sum = @sum + @i;
   SET @i = @i + 1;
   END correctly loops from 1 to N, adding each number to sum and incrementing i.

2. Step 2: Identify errors in other options

   DECLARE @sum INT = 0, @i INT = 1;
   WHILE @i < @N
   BEGIN
   SET @sum = @sum + @i;
   END misses increment of i, causing infinite loop. DECLARE @sum INT = 0, @i INT = 1;
   WHILE @i <= @N
   BEGIN
   SET @sum = @sum + @i;
   SET @i = @i - 1;
   END decrements i, causing infinite loop. DECLARE @sum INT = 0;
   WHILE @sum <= @N
   BEGIN
   SET @sum = @sum + 1;
   END incorrectly uses sum as loop variable.

3. Final Answer:

   DECLARE @sum INT = 0, @i INT = 1; WHILE @i <= @N BEGIN SET @sum = @sum + @i; SET @i = @i + 1; END -> Option D

4. Quick Check:

   Increment loop variable and add to sum = A [OK]

Hint: Increment loop counter and add to sum inside WHILE [OK]

Common Mistakes:

• Forgetting to increment loop variable
• Decrementing loop variable causing infinite loop
• Using sum as loop condition variable