stored procedure

Definitions

1. A named routine stored inside a database and executed there, combining SQL statements with procedural logic such as variables, loops, conditionals, and error handling. Applications call it by name with parameters instead of sending the SQL themselves. Each major database has its own language: PL/SQL in Oracle, T-SQL in SQL Server, and PL/pgSQL in PostgreSQL, which added CREATE PROCEDURE in version 11 alongside its long-standing functions. Stored procedures reduce network round-trips for multi-step operations, keep data-heavy logic close to the data, and can tighten security, since a user can be granted permission to execute a procedure without direct access to the underlying tables. Their traditional performance advantage from precompiled plans has narrowed, because modern databases cache plans for parameterised queries as well. The costs are business logic split between application and database, harder versioning, testing, and debugging, and vendor lock-in, which is why the choice remains a long-running debate between DBAs and application developers.

   In plain English: A mini-program that lives inside the database and runs SQL commands when you call it — like a recipe the database already knows by heart.

   Example: The legacy system has 400 stored procedures containing all the business logic, and the developers are terrified to change any of them because there are no tests.

Related Terms

• ORM
• Migration
• trigger
• Clause
• Database
• Dialect
• Procedure
• Subroutine
• CTE
• Window Function
• Lateral Join
• Join
• Join Table
• Knex
• Master-Slave
• Outer Join
• Partition
• Right Join
• Scan
• Subquery
• Write Lock
• MariaDB

Collections Including This Term

• Database Terminology