WHILE loops in procedures in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a WHILE loop in SQL procedures?

A WHILE loop repeatedly executes a block of SQL code as long as a specified condition is true.

beginner

How do you start a WHILE loop in a SQL procedure?

You start with the keyword WHILE followed by a condition, then the code block inside BEGIN and END.

beginner

Why is it important to change the condition inside a WHILE loop?

To avoid an infinite loop, you must update the condition inside the loop so it eventually becomes false.

intermediate

Example: What does this WHILE loop do? DECLARE @i INT = 1; WHILE @i <= 3 BEGIN PRINT @i; SET @i = @i + 1; END

It prints the numbers 1, 2, and 3 one by one, increasing @i by 1 each time until @i is greater than 3.

intermediate

Can WHILE loops be nested inside other WHILE loops in SQL procedures?

Yes, you can put a WHILE loop inside another WHILE loop to repeat complex tasks multiple times.

What keyword starts a WHILE loop in a SQL procedure?

A LOOP

B REPEAT

C FOR

D WHILE

The WHILE keyword is used to start a WHILE loop in SQL procedures.

What happens if the condition in a WHILE loop never becomes false?

A The loop stops automatically

B The loop runs forever (infinite loop)

C The procedure throws an error

D The loop runs once

If the condition never becomes false, the WHILE loop runs forever, causing an infinite loop.

Where do you put the code that repeats inside a WHILE loop?

A Between BEGIN and END

B After the WHILE keyword only

C Outside the procedure

D Inside a SELECT statement

The repeated code goes inside BEGIN and END blocks within the WHILE loop.

Which of these is a valid condition for a WHILE loop?

A SET @count = 0

B PRINT @count

C @count < 10

D DECLARE @count INT

Conditions in WHILE loops are boolean expressions like '@count < 10'.

Can you use a WHILE loop to repeat a task a fixed number of times?

A Yes, by controlling the loop condition

B No, WHILE loops run only once

C Only if you use a FOR loop inside

D Only in SELECT statements

You can repeat a task a fixed number of times by setting the WHILE loop condition accordingly.

Explain how a WHILE loop works inside a SQL procedure and why updating the loop condition is important.

Think about what happens if the condition never changes.

Describe a simple example of a WHILE loop in a SQL procedure that counts from 1 to 5.

Start with a variable set to 1 and increase it until 5.

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