Stored Procedures & Functions
Reusable, server-side SQL logic — and the real trade-off of putting logic in the database versus the application.
Overview
A stored procedure is a named, reusable block of SQL logic saved inside the database itself, callable by name instead of resending the full statement every time. A function is the same core idea, but it's required to return a value and can typically be used directly inside a query expression, like a `SELECT` column or a `WHERE` condition — not just called on its own. Both move logic from the application layer into the database layer, which is a real trade-off, not a free upgrade.
Why It Exists
Some operations genuinely need to happen atomically, as a single unit, no matter which application or service triggers them — and putting that logic in the database itself is the most reliable way to guarantee it happens the same way every time, regardless of which client calls it. Stored procedures and functions also cut down on network round trips for multi-step logic, since the whole sequence runs inside the database engine in one call instead of several separate round trips from the application.
Real World Example
A `transfer_funds(from_account, to_account, amount)` stored procedure debits one account and credits another as a single call. Internally, it wraps both the debit and the credit in one transaction, so either both happen or neither does — no application-level bug in any calling service can cause money to leave one account without arriving in the other, because the guarantee lives inside the database itself, not in whichever client happens to call it.
Example Data
accounts — before CALL transfer_funds(1, 2, 500)
| id | owner | balance |
|---|---|---|
| 1 | Sarah Johnson | 1000 |
| 2 | Michael Carter | 200 |
accounts — after: both rows changed together, inside one transaction
| id | owner | balance |
|---|---|---|
| 1 | Sarah Johnson | 500 |
| 2 | Michael Carter | 700 |
How It Works
A stored procedure is invoked with `CALL`, can accept parameters, can contain control flow like loops and conditionals, and may or may not return a value — it's built for running a sequence of operations. A function must return a value and, in most engines, can be used inline inside a query — as a computed column in a `SELECT`, or as part of a `WHERE` condition — which a procedure generally cannot do. Functions are also usually restricted from modifying data in most engines, while procedures are commonly used specifically to perform writes. The real decision underneath both is where business logic should live: inside the database, where it's guaranteed to run identically for every caller, or in the application layer, where it's easier to test, version, and review alongside the rest of your codebase.
Diagram
Multi-step logic, run atomically inside the database in one call
Application calls
transfer_funds(from, to, amount)
Debit from_account
Credit to_account
Result returned to caller
Common Mistakes
Putting significant business logic into stored procedures with no version control or tests
Why: Logic that lives only inside the database is invisible to the application's codebase and CI pipeline — it doesn't get reviewed, tested, or tracked the same way the rest of the system does.
Fix: Treat stored procedures as real code: keep the SQL in versioned migration files, and test them the same way you'd test any other logic.
Using a stored procedure purely to save a network round trip, for logic with no atomicity or reuse requirement
Why: It adds a hidden operational dependency and a second place the same logic can drift out of sync with the application, for a benefit that usually doesn't outweigh the cost.
Fix: Push logic into the database specifically when you need atomicity across multiple statements, a security boundary, or genuine reuse across many different clients — not as a default.
Confusing procedures and functions and trying to call a procedure directly inside a SELECT
Why: Most engines don't allow it — a function returns a value usable inside an expression, a procedure is invoked separately with CALL, and mixing up which one you need produces an engine error.
Fix: Know your specific engine's rules, but as a default: reach for a function when you need a value inside a query, and a procedure when you need to run a sequence of operations.
Interview Questions
What's the basic difference between a stored procedure and a function?
A function must return a value and can typically be used directly inside a query, like a SELECT column. A stored procedure doesn't have to return a value, is invoked with CALL rather than used inside an expression, and is generally used to run a sequence of operations.
Why might a team choose to put logic like a funds transfer in a stored procedure instead of application code?
Because the guarantee that the debit and the credit happen together needs to hold no matter which service or client initiates the transfer. Putting that logic inside the database, wrapped in a transaction, means the guarantee is enforced once, centrally, instead of depending on every calling application implementing it correctly.
What's the real downside of pushing a lot of business logic into stored procedures?
It fragments the codebase — logic that should be reviewable, testable, and versioned alongside the application instead lives in a place that's often outside normal CI/CD, harder to unit test, and easy for new engineers to miss entirely. The trade-off is real: you gain a strong atomicity/consistency guarantee at the database layer, but you pay for it with reduced visibility and testability of that logic, so it's worth reserving for cases where the guarantee is genuinely necessary.
Production Best Practices
Do
✓Reserve stored procedures for logic that genuinely needs atomicity, a security boundary, or true cross-client reuse.
✓Keep procedure/function SQL in versioned migration files, reviewed like any other code.
✓Test stored logic with the same rigor as application code.
Don't
✗Don't use a stored procedure purely to save a round trip for logic with no atomicity requirement.
✗Don't let significant business logic live only inside the database, invisible to the app's test suite.
✗Don't mix up procedure and function calling conventions — check your engine's exact rules.
Comparison
| Stored Procedure | Function | |
|---|---|---|
| Must return a value | No | Yes |
| Callable inside a SELECT | No (engine-dependent) | Yes |
| Can modify data | Yes, commonly | Usually restricted |
| Invoked with | CALL | Used as an expression |