In a relational database, what is the difference between a stored procedure and a stored function, and when would you reach for each?
answer
- function = expression, procedure = statement
- CALL vs embedded in SELECT
- procedure can COMMIT; function usually cannot
- optimizer chooses how often a function runs
- OUT params / result sets vs RETURN value
basics
~20 sA function returns a value and is called inside an expression or query. A procedure is invoked by a CALL statement, returns data through output parameters or result sets, and in most engines may commit or roll back. Functions compute values; procedures perform units of work.
solid answer
~50 s**Function**: declared with a return type and called *inside* an expression — a SELECT list, WHERE clause, join condition, CHECK constraint, generated column. It participates in query evaluation, so the engine decides how many times to call it and may reorder or skip calls. It normally cannot control transactions, because it runs inside the caller's statement. **Procedure**: a routine invoked with `CALL` as a statement in its own right. It has no return type; it hands data back through OUT/INOUT parameters or by emitting result sets. Because it is not embedded in another statement, most engines allow COMMIT/ROLLBACK inside its body. Rule of thumb: if it computes *a value from inputs* and you want it usable in queries, write a function. If it is a *unit of work* — validate, write three tables, commit, log — write a procedure. Side effects inside a function are legal in several engines but surprising, because the call count is the optimizer's choice, not yours.
code
sql · 18 linesCREATE FUNCTION order_total(p_order_id bigint)
RETURNS numeric
AS $$
SELECT sum(qty * unit_price) FROM order_line WHERE order_id = p_order_id;
$$ LANGUAGE sql;
SELECT o.id, order_total(o.id) FROM orders o WHERE order_total(o.id) > 1000;
CREATE PROCEDURE archive_old_orders(p_cutoff date)
AS $$
BEGIN
INSERT INTO orders_archive SELECT * FROM orders WHERE created_at < p_cutoff;
DELETE FROM orders WHERE created_at < p_cutoff;
COMMIT;
END;
$$;
CALL archive_old_orders(DATE '2024-01-01');go deeper
State the core recall: functions return a value and are used inside queries; procedures are invoked with CALL and do work. Give one example of each.
Add the mechanics — OUT/INOUT parameters, result sets, transaction control in procedures, and that the optimizer decides how often a function is evaluated.
Frame it as a design decision: functions compose into views/constraints/indexes, procedures own transaction boundaries and batching. Call out side effects in functions as a plan-dependent bug.
Discuss the routine layer as an API surface — EXECUTE grants replacing table grants, round-trip elimination for chatty workloads, and the deployment/versioning cost of code that lives in the catalog.
## Two shapes of server-side routine Every mainstream relational engine can store executable code in the catalog and run it inside the server process, next to the data. That code comes in two shapes, and the distinction is not cosmetic: it determines *where* the routine sits relative to a query, and therefore what it is allowed to do. ## Functions: expressions A function has a declared return type and is invoked as part of an expression. `SELECT id, tax(amount) FROM orders` embeds the call in the projection; `WHERE distance(a, b) < 10` embeds it in a predicate. The consequences follow from that placement: - **The optimizer owns the call count.** It may evaluate the function once per row, once per distinct input, once for the whole statement (if it can prove the result constant), or never at all — a predicate that short-circuits, a row filtered earlier, an eliminated partition. Never write code that assumes "it runs exactly N times". - **It runs inside someone else's statement.** A statement is atomic; letting a function commit halfway through would break that. So engines either forbid transaction control in functions or confine it to autonomous/isolated sub-transactions. - **It can be composed.** Functions appear in views, CHECK constraints, index definitions (when deterministic), and generated columns. That composability is the main reason to prefer a function when you have the choice. Functions may return a single scalar, a composite row, or a whole set of rows (table-valued / set-returning), depending on the engine. ## Procedures: statements A procedure has no return type. You invoke it with `CALL proc(args)`, which is a complete statement. That means it is *not* nested inside a query, and the classic capability follows: it can `COMMIT` and `ROLLBACK` in its body. That is what makes procedures the right tool for batch jobs, ETL steps, and multi-step maintenance work that must checkpoint progress rather than hold one giant transaction open. Procedures return data by two routes: **output parameters** (declared OUT or INOUT) and **result sets** — rows the procedure sends straight to the client, which the driver exposes as one or more cursors. Result sets are the traditional pattern in SQL Server and MySQL; PostgreSQL leans on OUT parameters and refcursors instead. ## The practical decision Ask what the thing *is*: - A derived value used by queries → **function**. `order_total(order_id)`, `normalize_phone(text)`, `is_business_day(date)`. - A named unit of work with side effects and its own transaction boundaries → **procedure**. `archive_old_orders(cutoff)`, `run_nightly_rollup()`. A useful smell test: if you would be uncomfortable with it running an unpredictable number of times, it should not be a function. ## Things that surprise people **Functions with side effects.** Most engines permit INSERT/UPDATE inside a function. It works — until the optimizer changes plans and the write happens a different number of times, or a partition prune skips it entirely. Treat it as a bug waiting for a plan flip. **Overloading and signatures.** Several engines identify routines by name *plus* argument types, so `f(int)` and `f(text)` coexist. Dropping or granting on the wrong overload is a common operational slip. **Error handling.** An exception raised in either kind of routine propagates to the caller and, by default, aborts the surrounding statement. Procedures that manage their own transactions must therefore catch, roll back, and decide explicitly whether to continue. **Permissions.** Both are catalog objects with EXECUTE privileges. Routines are the standard way to expose a *narrow* API to an application role: revoke table access, grant EXECUTE on a handful of routines, and let the routine do the privileged work. ## Where this leaves architecture Routines move computation next to the data, eliminating round trips — a loop of ten thousand single-row updates from the application becomes one CALL. That is a real, large win for chatty write workloads. The cost is that this logic lives outside your application's build, test, and deploy pipeline unless you deliberately drag it in via migrations. That tradeoff is a separate discussion; the mechanical difference between the two routine kinds is what an interviewer is checking here.
- Why can a procedure usually commit inside its body while a function usually cannot?A function is evaluated as part of an enclosing SQL statement, and a statement must be atomic — committing partway through would break that atomicity and leave the executor holding snapshots and locks that no longer match the transaction state. A procedure invoked by CALL is itself a top-level statement, so there is no enclosing statement whose atomicity would be violated. Engines that do allow commits in functions do so through autonomous transactions, which are a separate mechanism with their own hazards.
- A colleague writes an audit INSERT inside a scalar function used in a WHERE clause. What do you tell them?That the number of audit rows is now a property of the query plan, not the business logic. The optimizer may evaluate the predicate on far fewer rows than expected after an index change, may cache the result for repeated inputs, or may not evaluate it at all if another predicate filters first. The write should move into a procedure, a trigger, or explicit application code where the execution count is deterministic.
A function is an ingredient you drop into a recipe; a procedure is the recipe you run start to finish.
saying these in an interview costs you the question
- "A procedure is just a function that returns nothing" — it also differs in how it is invoked and in transaction control.
- Claiming functions can never have side effects; most engines allow them, they are simply unreliable in count.
- Assuming a scalar function in a SELECT list runs exactly once per output row.
- Saying procedures cannot return data at all — OUT parameters and result sets exist.
- Treating the choice as purely stylistic, ignoring that only functions compose into views, constraints, and indexes.