Stored Procedure
Subject: Computer and Technology noun /stɔːrd prəˈsiː.dʒər/ 1 definition
- relational-databases
- database-management
- database-programming
- software-engineering
-
Definition (noun)
Entry #239 · UpdatedA Stored Procedure is a named, reusable subroutine of SQL statements and procedural control-flow logic—such as variables, conditionals, loops, and exception handlers—stored directly inside a database management system. Applications invoke it on demand with an explicit statement (such as
CALLorEXEC), passing input or output parameters so the server executes multi-step operations locally and optionally returns status codes or result sets. Its core purpose is to keep complex data workflows close to the data, reducing network round trips, promoting execution-plan reuse, and enforcing security boundaries by allowing execution without direct privileges on underlying tables. Unlike a database function or trigger, a stored procedure is invoked explicitly rather than embedded inside SQL queries or fired automatically by table events, and it can manage transactions directly.Common Types
User-Defined Stored Procedures: Custom routines written by developers in a procedural SQL dialect (such as T-SQL, PL/SQL, or PL/pgSQL) to encapsulate application business logic, batch updates, or multi-step workflows.
System Stored Procedures: Built-in administrative routines shipped with the database engine (such as
sp_*routines in SQL Server orSYSPROCroutines in DB2) to inspect catalog metadata, configure settings, and run maintenance tasks.External Stored Procedures: Routines implemented in an external programming language—such as C, Java, .NET (CLR), or Python—and registered with the database to access system APIs, external libraries, or specialized computation.
Temporary Stored Procedures: Session-scoped routines (such as
#temp_procin SQL Server) created for short-lived batch scripts or testing and automatically dropped when the connection closes.
Example
During checkout, a backend service invokes
CALL place_order(4821, 903, 2)with a customer ID, product ID, and quantity rather than sending separate queries over the network. Within a single atomic transaction, the database verifies available stock, decrements the inventory count, inserts the order record, and returns the newly generated order ID.