Skip to main content
Definition Website Computer and Technology

Stored Procedure

Subject: Computer and Technology noun /stɔːrd prəˈsiː.dʒər/ 1 definition

  1. Definition (noun)

    Entry #239 · Updated

    A 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 CALL or EXEC), 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 or SYSPROC routines 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_proc in 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.