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?"