Stored routines – when they are worth it

A look at stored routines. Procedures and functions in MySQL – and when they earn their place. The default stance for web apps: keep business logic in application code; use stored routines for specific, justified cases.

Procedures vs functions

• Stored procedure – called with CALL; can return multiple result sets; may modify data
• Stored function – returns a single value; usable inside SELECT expressions; stricter about side effects

When they are worth it

• Complex reporting maintained by DBAs, consumed by many clients
• Batch maintenance jobs run only on the server (archive old rows, rebuild summaries)
• Guaranteeing identical logic across apps written in different languages
• Reducing round trips for many small SQL steps on high-latency connections

When your PHP or Python layer already owns validation, auth, and transactions, duplicating that in stored procedures creates two places to fix bugs.

Example procedure

DELIMITER //

CREATE PROCEDURE sp_cancel_order(IN p_order_id INT UNSIGNED)
BEGIN
  DECLARE v_status VARCHAR(20);

  START TRANSACTION;

  SELECT status INTO v_status
  FROM orders
  WHERE id = p_order_id
  FOR UPDATE;

  IF v_status NOT IN ('pending', 'paid') THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Order cannot be cancelled';
  END IF;

  UPDATE orders SET status = 'cancelled' WHERE id = p_order_id;

  UPDATE products p
  INNER JOIN order_items oi ON oi.product_id = p.id
  SET p.stock = p.stock + oi.quantity
  WHERE oi.order_id = p_order_id;

  COMMIT;
END //

DELIMITER ;

CALL sp_cancel_order(101);

That bundles checks, status update, and stock return in one server-side unit. Reasonable if every client must cancel orders identically. If only your Laravel app ever cancels orders, the same logic in a service class is easier to test and version.

Example function

DELIMITER //

CREATE FUNCTION fn_order_total(p_order_id INT UNSIGNED)
RETURNS DECIMAL(12, 2)
DETERMINISTIC
READS SQL DATA
BEGIN
  DECLARE total DECIMAL(12, 2);
  SELECT SUM(quantity * unit_price) INTO total
  FROM order_items
  WHERE order_id = p_order_id;
  RETURN IFNULL(total, 0);
END //

DELIMITER ;

SELECT id, fn_order_total(id) AS total FROM orders;

Handy in ad-hoc SQL; in app code a SUM in a query or ORM method is usually enough.

Triggers – quick mention

Triggers run automatically on insert/update/delete. Audit logs and maintaining denormalised counts are classic uses. They are invisible to application developers who do not know they exist – that is the main risk. Prefer explicit code unless the rule must fire for every access path including manual SQL.

CREATE TRIGGER trg_orders_audit
AFTER UPDATE ON orders
FOR EACH ROW
INSERT INTO order_audit (order_id, old_status, new_status, changed_at)
VALUES (OLD.id, OLD.status, NEW.status, NOW());

Downsides to remember

• Harder to unit test than application code
• Not in git the same way unless you migration-manage them
• Debugging stepping through PHP then mysteriously failing in a trigger is miserable
• Vendor lock-in to MySQL syntax

Rule of thumb

Start with queries, views, and app-layer services. Add stored routines when you have a concrete reason – not because “logic belongs in the database” sounded clever on a forum.

The next tutorial covers EXPLAIN – reading query plans so you know whether indexes work and why a report suddenly got slow.