Stored Procedure

ELI5 — The Vibe Check

A stored procedure is a named program you write in SQL (and sometimes a procedural language) that lives inside the database. Instead of sending multiple queries from your app, you call one procedure and the database handles the logic. Like a function, but lives in the database.

Real Talk

A stored procedure is a precompiled collection of SQL statements stored in the database. It can accept parameters, contain conditional logic, loops, and error handling. Procedures are called by name and can encapsulate complex business logic, reducing network round-trips and improving security.

Show Me The Code

CREATE PROCEDURE transfer_funds(
  from_account INT,
  to_account INT,
  amount DECIMAL
)
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE accounts SET balance = balance - amount WHERE id = from_account;
  UPDATE accounts SET balance = balance + amount WHERE id = to_account;
END;
$$;

When You'll Hear This

"The payment logic lives in a stored procedure." / "Stored procedures reduce the number of round-trips between the app and database."