Error handling in procedures in MySQL - Time & Space Complexity

Time Complexity: Error handling in procedures

O(n)

Understanding Time Complexity

When we use error handling in MySQL procedures, we want to know how the time to run changes as the data grows.

We ask: How does adding error checks affect the speed when the procedure runs on more data?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

CREATE PROCEDURE update_stock()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE v_product_id INT;
  DECLARE cur CURSOR FOR SELECT id FROM products;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_product_id;
    IF done THEN
      LEAVE read_loop;
    END IF;
    UPDATE inventory SET quantity = quantity - 1 WHERE product_id = v_product_id;
  END LOOP;
  CLOSE cur;
END;
    

This procedure loops through all products and updates inventory, using error handling to detect the end of the cursor.

Identify Repeating Operations

• Primary operation: Looping through each product with a cursor.
• How many times: Once for each product in the products table.

How Execution Grows With Input

As the number of products grows, the procedure runs the update for each one, plus checks for the end of the cursor each time.

Input Size (n)	Approx. Operations
10	About 10 updates and cursor fetch checks
100	About 100 updates and cursor fetch checks
1000	About 1000 updates and cursor fetch checks

Pattern observation: The work grows directly with the number of products; more products mean more updates and cursor fetch checks.

Final Time Complexity

Time Complexity: O(n)

This means the time to run grows in a straight line with the number of products processed.

Common Mistake

[X] Wrong: "Adding error handling makes the procedure run much slower, like squared time."

[OK] Correct: Error handling here only adds a small check each loop, so it grows linearly, not much slower.

Interview Connect

Understanding how error handling affects procedure speed helps you write reliable code that scales well, a useful skill in real projects.

Self-Check

"What if we added nested loops inside the procedure? How would the time complexity change?"