Stored Procedures

Advanced

⏱️ 15 mins read

Wraps the employees (Topic 1) and orders (Topic 7) queries you already know into reusable, server-side objects — MySQL syntax.

What You'll Learn

A stored procedure is a reusable block of SQL code stored in the database. It accepts IN (input), OUT (output), and INOUT parameters, can contain conditional logic, loops, and transactions. Benefits: reduces network round-trips, centralizes business logic, enables access control.

Syntax

CREATE PROCEDURE name(IN param TYPE)
BEGIN
  -- SQL statements
END;

CALL procedure_name(args);

Example

DELIMITER $$
CREATE PROCEDURE GetDeptReport(
  IN dept_id INT,
  IN min_salary DECIMAL(10,2)
)
BEGIN
  SELECT
    name, salary, hire_date,
    DATEDIFF(NOW(), hire_date) AS days_employed
  FROM employees
  WHERE department_id = dept_id
    AND salary >= min_salary
  ORDER BY salary DESC;
END$$
DELIMITER ;

-- Call it
CALL GetDeptReport(1, 80000);   -- Ada and Grace

Beyond the Basics

Data in play — reference tables for this topic
-- employees (from Topic 1)
-- id | name    | department_id | salary | hire_date
-- 1  | Ada     | 1             | 145000 | 2021-03-01
-- 2  | Grace   | 1             | 115000 | 2022-06-13
-- 3  | Alan    | 2             | 92000  | 2020-11-02
-- 4  | Edsger  | NULL          | 78000  | 2023-08-19
-- 5  | Barbara | 3             | 64000  | 2024-01-08

-- orders (from Topic 7)
-- id | total  | order_date
-- 1  | 240.00 | 2024-01-15
-- 2  | 89.90  | 2024-02-03
-- 3  | 430.00 | 2024-03-11
-- 4  | 59.50  | 2024-04-02
-- 5  | 120.00 | 2024-05-20

One round trip instead of five

The app-side version of GetDeptReport is: connect, send query, wait, transfer rows — per report. A procedure lives inside the server: one CALL carries the parameters in and the result set out, and multi-step logic (validate, insert, audit) never crosses the network between steps. On a chatty microservice topology, that latency difference is architectural.

-- App side: 5 statements, 5 round trips, 5 chances to fail halfway
-- Procedure side:
CALL GetDeptReport(1, 80000);
-- Server executes the whole body next to the data
Procedures buy latency and atomicity by moving logic next to the data — and charge you in portability (next section).

Procedure vs function vs view — pick the right container

Three ways to store logic in the database, three contracts: a VIEW returns rows and takes no parameters (Topic 19); a FUNCTION computes a value and can appear inside SELECT (so it must not have side effects); a PROCEDURE runs on request, can do anything — INSERT, UPDATE, COMMIT — and returns nothing unless asked. Choosing wrong means fighting the engine's rules.

-- Value per row → function:
SELECT name, tenure_years(hire_date) FROM employees;

-- Saved row-set → view:
SELECT * FROM customer_summary WHERE order_count = 0;

-- Side effects + flow control → procedure:
CALL promote_employee(4, 85000);
Ask 'what does it return and may it change data?' — rows/never → view, value/never → function, anything/anything → procedure.

The portability and versioning tax

The DELIMITER dance in our example is MySQL-only; PostgreSQL uses CREATE PROCEDURE ... $$ ... $$ with no delimiter trick; SQL Server uses BEGIN...END and T-SQL types. A procedure-heavy database cannot be migrated by moving tables — the business logic itself must be ported. And procedures living in the server evade normal git workflows unless your migrations script them.

-- Same procedure, PostgreSQL dialect:
CREATE PROCEDURE get_dept_report(dept_id INT, min_salary NUMERIC)
LANGUAGE sql AS $$
  SELECT name, salary, hire_date,
         CURRENT_DATE - hire_date AS days_employed
  FROM employees
  WHERE department_id = dept_id AND salary >= min_salary
  ORDER BY salary DESC;
$$;
-- Different types (NUMERIC), different date math, different body language
Treat procedures like code: keep their definitions in migration files under version control, and write them knowing a database migration may mean a rewrite.

Common Mistakes

Stored procedures are harder to version-control, test, and debug than application code. They couple business logic to the database. Over-using them can make the system harder to migrate to a different database.

Interview Tips

Know the difference: Stored Procedure (can have side effects, doesn't always return a value) vs Function (always returns a value, can be used in SELECT). Mention that procedures reduce network latency for complex multi-step operations.

Official References

Test Yourself — 6 questions
Self-check · 0/6 answered

1. The core latency argument for stored procedures is…?

2. Which container fits 'returns rows, takes no parameters, no side effects'?

3. The DELIMITER $$ dance in the example is…?

4. Can a stored procedure contain COMMIT and ROLLBACK?

5. CALL GetDeptReport(1, 80000) on ShopCo returns…?

6. Why do critics say procedures 'evade version control'?

Practice

Return each department's id, name, and employee count — the result set a get_department_headcounts() stored procedure would return. Use a LEFT JOIN so departments with no employees still appear.

⚡ Solve it in the SQL playground →

Frequently Asked Questions

Can a stored procedure contain COMMIT and ROLLBACK?

In most engines, yes — procedures can run full transactions, which makes them a natural place to wrap multi-table invariants (Topic 23). But a COMMIT inside a procedure commits the CALLER's transaction too, so APIs built on procedures need clear ownership rules about who may end a transaction.

Why do some engineering teams ban stored procedures?

Logic in the database is hard to unit test, invisible to application profilers, database-specific, and often edited directly in prod by people with access. The counter-argument is latency and data-locality. Mature teams usually allow procedures for performance-critical, well-tested operations — and keep business rules in application code.

Back to Full Roadmap