Loading…
Loading…
SQL tutorial · Advanced
⏱️ 15 mins read
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.
CREATE PROCEDURE name(IN param TYPE)
BEGIN
-- SQL statements
END;
CALL procedure_name(args);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| 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 |
| 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 |
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 dataThree 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);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 languageStored 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.
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.
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'?
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 →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.
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.