Bird
Raised Fist0
SQLquery~5 mins

WHILE loops in procedures in SQL

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Introduction

WHILE loops help repeat a set of actions in a procedure until a condition is no longer true. This saves time and avoids writing the same code many times.

When you want to process rows or data repeatedly until a condition changes.
When you need to perform a task multiple times but don't know the exact number in advance.
When you want to automate repetitive database tasks inside a procedure.
When you want to build a counter or accumulator inside a stored procedure.
When you want to loop through numbers or dates to insert or update data.
Syntax
SQL
WHILE condition
BEGIN
  -- statements to repeat
END

The condition is checked before each loop. If it is true, the loop runs.

Use BEGIN and END to group multiple statements inside the loop.

Examples
This loop prints numbers from 1 to 5 by increasing the counter each time.
SQL
WHILE @counter <= 5
BEGIN
  PRINT @counter;
  SET @counter = @counter + 1;
END
This loop runs as long as there are tasks with status 'Pending'. It updates one task to 'Done' each time.
SQL
WHILE EXISTS (SELECT 1 FROM Tasks WHERE Status = 'Pending')
BEGIN
  -- process pending tasks
  UPDATE TOP (1) Tasks SET Status = 'Done' WHERE Status = 'Pending';
END
Sample Program

This procedure prints 'Loop number: 1', then 2, then 3 using a WHILE loop.

SQL
DECLARE @counter INT = 1;
WHILE @counter <= 3
BEGIN
  PRINT CONCAT('Loop number: ', @counter);
  SET @counter = @counter + 1;
END
OutputSuccess
Important Notes

Make sure the loop condition will eventually become false to avoid infinite loops.

You can use variables inside the loop to control how many times it runs.

WHILE loops are useful but sometimes set-based SQL queries are faster and simpler.

Summary

WHILE loops repeat code while a condition is true.

Use them inside procedures to automate repeated tasks.

Always update variables inside the loop to avoid endless repetition.

Practice

(1/5)
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 <= @N
BEGIN
SET @sum = @sum + 1;
END
B. DECLARE @sum INT = 0, @i INT = 1;
WHILE @i < @N
BEGIN
SET @sum = @sum + @i;
END
C. DECLARE @sum INT = 0, @i INT = 1;
WHILE @i <= @N
BEGIN
SET @sum = @sum + @i;
SET @i = @i - 1;
END
D. DECLARE @sum INT = 0, @i INT = 1;
WHILE @i <= @N
BEGIN
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
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard