What are stored procedures and when should you use them?

Question

Accepted Answer

A **stored procedure** is a set of SQL statements (and procedural logic) saved in the database and executed by name. It runs logic *inside* the database rather than in application code. They have real benefits but also significant trade-offs, so when to use them is a genuine design decision.

## A stored procedure

```sql
-- define a procedure that encapsulates logic in the database
CREATE PROCEDURE transfer_funds(from_id INT, to_id INT, amount DECIMAL)
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE accounts SET balance = balance - amount WHERE id = from_id;
  UPDATE accounts SET balance = balance + amount WHERE id = to_id;
  -- can include logic: conditionals, loops, error handling, transactions
END;
$$;

CALL transfer_funds(1, 2, 100);   -- execute by name
```

A stored procedure bundles SQL and procedural logic (variables, conditionals, loops, error handling) into a named, reusable database routine. Functions are similar but return a value.

## Potential benefits

```text
✓ Performance — runs IN the database (no round-trips for multi-step logic); precompiled
✓ Reduced network traffic — one call vs many queries from the app
✓ Reusability — shared logic callable from multiple applications/clients
✓ Security — grant EXECUTE on a procedure without granting table access; reduces
  injection surface (parameterized)
✓ Consistency — centralize critical logic so all clients use the same implementation
```

## The significant trade-offs (why many avoid them today)

```text
✗ Business logic in the DATABASE → harder to version-control, test, and maintain
  (logic split between app code and the database)
✗ Database-specific (PL/pgSQL vs T-SQL vs PL/SQL) → less portable, vendor lock-in
✗ Harder to debug than application code; weaker tooling
✗ Scaling — the database is often the hardest tier to scale; pushing logic there
  can increase its load
✗ Modern apps often prefer logic in the application layer (testable, portable, versioned)
```

## When to use them

```text
✓ Performance-critical data-intensive operations (heavy multi-step DB logic, batch jobs)
✓ Logic that MUST be enforced at the DB level across multiple clients
✓ Reducing round-trips for operations involving many queries
✗ AVOID putting general business logic in procedures by default — modern practice
  usually favors the application layer (better testing, versioning, portability)
```

## Why it matters

Understanding stored procedures — including their trade-offs — is valuable knowledge because they represent a genuine architectural choice with real benefits and real drawbacks, and knowing *when* to use them (and when not to) reflects mature judgment.

Stored procedures can offer **performance benefits** (running multi-step logic inside the database avoids network round-trips), reduced network traffic, reusability across clients, security (granting execute without table access), and centralized consistency — making them appropriate for performance-critical data-intensive operations, batch processing, and logic that must be enforced at the database level across multiple applications.

However, understanding the **significant trade-offs** is equally important and reflects modern practice: putting business logic in the database makes it harder to version-control, test, and maintain (splitting logic between app and database), ties you to a specific database's procedural language (PL/pgSQL, T-SQL, PL/SQL — reducing portability), is harder to debug, and adds load to the database tier (often the hardest to scale).

For these reasons, **modern application development often favors keeping business logic in the application layer** (testable, portable, version-controlled) rather than in stored procedures.

The key insight is the **judgment of when to use them** — appropriate for specific performance-critical or DB-level-enforcement needs, but generally avoided as a default for business logic.

Since stored procedures are common in many systems (especially enterprise/legacy), and since understanding both their benefits and their trade-offs (and the modern lean toward application-layer logic) is what enables sound architectural decisions, understanding stored procedures is valuable senior-level knowledge that demonstrates the ability to weigh database-side vs application-side logic — a meaningful design consideration and a topic reflecting architectural maturity.