CREATE PROCEDURE syntax in SQL - Time & Space Complexity

Time Complexity: CREATE PROCEDURE syntax

O(n)

Understanding Time Complexity

When we create a procedure in SQL, we want to know how its running time changes as the data grows.

We ask: How does the procedure's work increase when the input gets bigger?

Scenario Under Consideration

Analyze the time complexity of the following procedure.

CREATE PROCEDURE GetEmployeesByDept(IN dept_id INT)
BEGIN
  SELECT * FROM Employees
  WHERE DepartmentID = dept_id;
END
    

This procedure selects all employees from a specific department.

Identify Repeating Operations

Look for repeated actions inside the procedure.

• Primary operation: Scanning the Employees table to find matching rows.
• How many times: Once per row in Employees, checking if DepartmentID matches.

How Execution Grows With Input

The procedure checks each employee to see if they belong to the department.

Input Size (n)	Approx. Operations
10	10 checks
100	100 checks
1000	1000 checks

Pattern observation: The work grows directly with the number of employees.

Final Time Complexity

Time Complexity: O(n)

This means the procedure takes longer in a straight line as the employee count grows.

Common Mistake

[X] Wrong: "The procedure runs instantly no matter how many employees there are."

[OK] Correct: The procedure must check each employee to find matches, so more employees mean more work.

Interview Connect

Understanding how procedures scale helps you explain database performance clearly and confidently.

Self-Check

"What if the procedure used an index on DepartmentID? How would the time complexity change?"