Skip to content
uencoder

Stored Procedure

A stored procedure is a precompiled SQL program stored in the database that can be invoked on demand, reducing network round trips and SQL-injection risk.

Purpose

To push business logic down into the database, reducing network round trips and repeated parsing.

Mechanism

Written in SQL/PL (PL/pgSQL, T-SQL), precompiled with cached execution plans. Accepts parameters, returns result sets or affected-row counts. Executes inside the database, cutting round trips, with controllable privileges. But hard to debug, version, and decouple from the database.

Use cases

  • Encapsulating complex business logic
  • Batch data processing
  • Privilege-isolated database operations
  • Legacy system logic

Examples

  • Input 
    CALL transfer(account_a, account_b, 100)
    Output 
    Stored procedure invoked

    Precompiled

Common errors

Over-pushing business logic down hurts maintainability
Keep core logic in the app; use procedures only for data-intensive operations

Related terms

Trigger
A trigger is database code that executes automatically when specific events (INSERT/UPDATE/DELETE) occur, used for auditing, cascading, and constraints.
GLOSSARY
View
A view is a stored query definition (a virtual table) holding no data, computed dynamically at query time, used to simplify queries, isolate security, and abstract.
GLOSSARY
SQL
SQL (Structured Query Language) is the standard query language of relational databases, defining and manipulating data declaratively (DDL/DML/DQL/DCL).
GLOSSARY

References