Parameters (IN, OUT, INOUT) in SQL - Time & Space Complexity

Time Complexity: Parameters (IN, OUT, INOUT)

O(1)

Understanding Time Complexity

When using parameters in SQL procedures, it's important to see how the number of operations changes as input grows.

We want to know how the procedure's work scales with the size of input data passed through parameters.

Scenario Under Consideration

Analyze the time complexity of this stored procedure using IN, OUT, and INOUT parameters.

CREATE PROCEDURE UpdateScores(
  IN student_id INT,
  IN new_score INT,
  OUT old_score INT,
  INOUT total_updates INT
)
BEGIN
  SELECT score INTO old_score FROM Scores WHERE id = student_id;
  UPDATE Scores SET score = new_score WHERE id = student_id;
  SET total_updates = total_updates + 1;
END;
    

This procedure updates a student's score, returns the old score, and increments a counter of total updates.

Identify Repeating Operations

Look for repeated actions or loops in the procedure.

• Primary operation: Single SELECT and UPDATE statements each run once per call.
• How many times: Each operation runs exactly once per procedure execution.

How Execution Grows With Input

The procedure does a fixed number of steps regardless of input size.

Input Size (n)	Approx. Operations
10	3 (1 SELECT, 1 UPDATE, 1 SET)
100	3 (same fixed steps)
1000	3 (still the same fixed steps)

Pattern observation: The number of operations stays the same no matter how big the input values are.

Final Time Complexity

Time Complexity: O(1)

This means the procedure takes the same amount of time no matter the input size.

Common Mistake

[X] Wrong: "The procedure takes longer if the input numbers are bigger."

[OK] Correct: The procedure runs a fixed number of steps regardless of input values; bigger numbers don't mean more work here.

Interview Connect

Understanding how parameters affect procedure work helps you explain efficiency clearly and confidently in real situations.

Self-Check

"What if the procedure included a loop that updated multiple rows based on an input list? How would the time complexity change?"