Logic Stored in the Database
A stored procedure is a named program written in SQL or a procedural dialect that lives inside the database. Applications call it by name, pass arguments, and the database runs the whole block server side.
Why Use One
- It cuts round trips because many statements run in one call.
- It centralizes a business rule so every app uses the same logic.
- It can wrap several statements in one transaction for consistency.
- Permissions can be granted on the procedure rather than the raw tables.
Procedures vs Functions
A stored procedure is called with a CALL style statement and often performs actions or returns multiple results. A function returns a single value and can be used inside a query expression.
Tradeoffs
Procedural logic in the database is harder to version control and test than application code, and it can lock you into one vendor dialect. Keep complex business rules where your team can test them well.
Key idea
A stored procedure runs a named block of SQL inside the database, reducing round trips and centralizing logic at the cost of harder testing and vendor lock in.