WHILE loops in procedures in SQL - Time & Space Complexity

Time Complexity: WHILE loops in procedures

O(n)

Understanding Time Complexity

When using WHILE loops in SQL procedures, it is important to understand how the number of steps grows as the loop runs more times.

We want to know how the total work changes when the loop runs many times.

Scenario Under Consideration

Analyze the time complexity of the following SQL procedure using a WHILE loop.

CREATE PROCEDURE CountToN(@n INT)
AS
BEGIN
  DECLARE @i INT = 1;
  WHILE @i <= @n
  BEGIN
    -- Some simple operation
    SET @i = @i + 1;
  END
END
    

This procedure counts from 1 up to a number n, doing a simple step each time.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: The WHILE loop runs a simple step repeatedly.
• How many times: It runs once for each number from 1 up to n.

How Execution Grows With Input

As n gets bigger, the number of loop steps grows directly with n.

Input Size (n)	Approx. Operations
10	About 10 steps
100	About 100 steps
1000	About 1000 steps

Pattern observation: The work grows evenly as n grows; doubling n doubles the steps.

Final Time Complexity

Time Complexity: O(n)

This means the total work grows in a straight line with the size of n.

Common Mistake

[X] Wrong: "The loop runs in constant time no matter how big n is."

[OK] Correct: Each loop step happens once per number up to n, so more n means more steps.

Interview Connect

Understanding how loops affect time helps you explain how your code scales and shows you can think about efficiency clearly.

Self-Check

"What if we added a nested WHILE loop inside the first one that also runs n times? How would the time complexity change?"

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