Stored procedure

On this page 7

A stored procedure is a named routine of SQL statements and procedural logic that is stored in the database catalog and executed on the database server when a client calls it by name, usually with input and output parameters. Within database fundamentals, it is the main way to package business logic, data validation and multi-step operations close to the data itself.

Stored procedures are written in vendor dialects such as PL/SQL in Oracle, T-SQL in SQL Server and PL/pgSQL in PostgreSQL, and the SQL standard describes them under SQL/PSM. They reduce network round trips, centralize rules shared by many applications and allow fine-grained permissions, but they also tie logic to one database engine and complicate versioning and testing.

How a stored procedure executes on the server

A stored procedure executes entirely inside the database engine process, so the client sends one call and receives results instead of sending every statement separately. When the procedure is created, the DBMS parses and validates the code and stores it in the system catalog. On execution, the engine binds parameters, builds or reuses an execution plan for each statement and runs the procedural control flow.

Plan reuse is a major benefit. SQL Server and others keep compiled plans in a plan cache, which avoids repeated optimization. This also creates a known risk called parameter sniffing: a plan optimized for the first parameter values can perform badly for later values with very different data distributions, so engines offer recompilation hints and plan guides.

The table below lists the typical building blocks available inside a procedure body.

ElementPurposeExample syntax
ParametersPass values in and outIN, OUT, INOUT modes
Local variablesHold intermediate valuesDECLARE v_total NUMERIC
Control flowBranching and loopsIF, WHILE, LOOP
Error handlingCatch and respond to failuresTRY...CATCH, EXCEPTION blocks
Transaction controlCommit or roll back workCOMMIT, ROLLBACK
Result setsReturn rows to the callerSELECT or cursor output
Typical elements of a stored procedure

Stored procedures compared with functions, triggers and views

A stored procedure differs from related database objects mainly in how it is invoked and what it may do. Procedures are called explicitly with CALL or EXEC, may modify data and, in most engines, may manage transactions. User-defined functions return a value and can be used inside queries, but they are usually restricted from side effects. A database trigger runs automatically in response to events, and a database view is a stored query without procedural logic.

ObjectHow it runsCan modify dataTypical use
Stored procedureExplicit callYesMulti-step business operations
User-defined functionInside a query expressionUsually noReusable calculations
Database triggerAutomatically on eventsYesAuditing, derived columns
Database viewReferenced in a queryLimitedSimplified or secured reads
Comparison with related database objects

Exact rules vary by engine. In PostgreSQL, the CREATE PROCEDURE command was added in version 11 and allows transaction control, while earlier code used functions for the same purpose. Engineers should check the vendor documentation for transaction semantics, because behavior differs considerably.

Benefits of stored procedures in practice

Stored procedures provide value through performance, security and consistency. Each benefit depends on context, and none is automatic; a poorly written procedure can be slower than well-designed application code.

BenefitMechanismWhen it matters most
Fewer round tripsMany statements run in one callHigh-latency networks, chatty workflows
Permission controlGrant execute without table accessRegulated or shared databases
Injection resistanceTyped parameters instead of string buildingInput from untrusted users
Centralized rulesOne implementation for all clientsSeveral applications share data
Set-based processingData stays on the serverBulk updates over millions of rows
Main benefits and their conditions

The security benefit is real but conditional. A procedure protects against SQL injection only if it uses parameters correctly; procedures that concatenate strings into dynamic SQL remain vulnerable. Ownership chaining and definer rights, such as SECURITY DEFINER in PostgreSQL, let a procedure perform actions the caller cannot perform directly, which demands careful review.

Limitations and design trade-offs

The main drawback of stored procedures is tight coupling to a specific database engine. Procedural dialects are not portable, so migrating from one DBMS to another often means rewriting the logic. Business rules split between application code and procedures also make the system harder to reason about and to test.

  • Versioning and deployment: procedures must be managed through migration scripts and source control, or they drift between environments.
  • Testing difficulty: unit tests require a running database, and frameworks such as tSQLt or pgTAP add tooling overhead.
  • Scalability: logic running on the database consumes its CPU, which is usually the hardest tier to scale horizontally.
  • Debugging and observability: tracing, logging and profiling tools are weaker than those for application languages.

Many teams therefore follow a balanced approach: keep data-intensive, set-based and integrity-critical operations in procedures, and keep orchestration, external calls and presentation logic in the application layer.

When to use a stored procedure

A stored procedure is the right choice when logic is data-heavy, shared or security-sensitive. The decision table below summarizes common situations encountered in OLTP and reporting systems.

SituationRecommended choiceReason
Bulk transformation of large tablesStored procedureAvoids moving data over the network
Logic shared by several applicationsStored procedureSingle source of truth
Calls to external web servicesApplication codeDatabases handle outbound calls poorly
Need for multi-vendor portabilityApplication code or ORMProcedural dialects differ
Strict least-privilege accessStored procedureExecute rights replace table rights
Decision guide for stored procedures

The short version

Think of a stored procedure as a saved recipe kept in the database kitchen. Instead of reading out each step to the cook every time, an application simply says the recipe name and supplies the ingredients. The database follows the steps itself and hands back the finished result.

This saves time, keeps everyone using the same recipe and lets the kitchen decide who may cook what. The trade-off is that the recipe is written in that kitchen’s own language, so moving to another kitchen means rewriting it.

Where it makes a difference

A regional retailer closes its books every night by moving the day’s sales into summary tables. The original application fetched about 4 million order lines over the network, calculated totals in memory and wrote the results back, a process that took nearly 90 minutes and sometimes overlapped with the morning opening.

The database team rewrote the job as a stored procedure that performs set-based INSERT ... SELECT aggregation, validates totals against control figures and wraps the work in a single transaction with error handling. The nightly job now runs in under 10 minutes, a failed run rolls back cleanly, and the reporting service receives only execute permission on the procedure instead of write access to the sales tables.