⚙️Stored procedures

Logic that lives next to the data

Stored procedures combine SQL with control flow on the server, useful for multi-step transactions, batch maintenance, and cutting round trips. They are also a governance surface: callers need only EXECUTE while the routine runs with definer-defined privileges.

SQL

PostgreSQL-style procedure sketch

CREATE OR REPLACE PROCEDURE core.close_stale_batches(IN max_age interval)
LANGUAGE plpgsql
AS $
BEGIN
  UPDATE ingest.batches
  SET status = 'CLOSED'
  WHERE status = 'OPEN'
    AND opened_at < now() - max_age;
END;
$;

CALL core.close_stale_batches(interval '7 days');

✓Illustrative result set

rows_updated
18

⚡ Advanced / dialect note

Procedures differ from functions in PostgreSQL: procedures can commit or rollback internally when allowed; functions are usually intended as expressions inside queries.

Hidden monolith in the catalog

Untested branching that diverges across staging and production.
Tight coupling that makes CI/CD and schema evolution harder than app-shipped SQL.
Broad EXECUTE grants on routines that touch more than callers should see.

Small, versioned, tested routines

Version procedure bodies with migrations like application code.
Document inputs, outputs, transaction boundaries, and side effects.
Prefer several focused procedures over one mega-script.

📌When they shine

Stable, data-local work, not all business rules.

Good fit

Month-end close, bulk reconciliation, controlled ETL steps, and routines where one round trip and strict atomicity matter.

Caution

Sprawling domain logic only in SQL becomes hard to review next to services, keep orchestration where your team can test and observe it.

Security

On engines that support EXECUTE AS or equivalent, treat elevated privilege inside procedures as a deliberate contract: narrow object access, audit who can run which routine, and avoid dynamic SQL without binds.

TL;DR

Stored procedures bundle imperative SQL (loops, conditionals, multiple statements) inside the database for reuse, permissions, and sometimes lower network round trips. They run in the database security context, review carefully for dynamic SQL injection and deployment drift. Many teams prefer application-layer logic for portability, but procedures remain valuable for tight transactional routines and vendor-specific optimizations.