Stored Procedures

A stored procedure is a named block of procedural SQL logic — variables, conditionals, loops, multiple statements — that is saved inside the database itself and invoked by name instead of being sent from the application every time. Rather than an application executing a sequence of separate SQL statements over the network, it can call one procedure and let the database run the whole sequence internally.

Why stored procedures exist

Reuse — the same logic can be called from many applications or scripts without duplicating SQL in each one.
Reduced network round-trips — several statements execute inside the database in one call instead of many separate queries from the client.
Centralized business rules — logic like "when placing an order, deduct stock and record the transaction" lives in one place, next to the data it operates on.
Access control — a user can be granted permission to execute a procedure without being granted direct access to the underlying tables it touches.

Basic syntax: CREATE PROCEDURE and CALL

The exact syntax differs by database, but the shape is consistent: define the procedure once with CREATE PROCEDURE, then invoke it repeatedly with CALL (or EXEC, depending on the dialect). Here is a simple PostgreSQL example that records a payment and marks an invoice as paid:

PostgreSQL: a procedure with an input parameter

SQL

CREATE PROCEDURE mark_invoice_paid(p_invoice_id INT, p_amount NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO payments (invoice_id, amount, paid_at)
  VALUES (p_invoice_id, p_amount, NOW());

  UPDATE invoices
  SET status = 'paid'
  WHERE invoice_id = p_invoice_id;
END;
$$;

-- Invoke it
CALL mark_invoice_paid(1001, 249.99);

Both the INSERT and the UPDATE run as part of a single call, and (depending on how the procedure and surrounding transaction are configured) either both succeed or neither does, keeping payments and invoices consistent without the application needing to orchestrate two separate statements itself.

A procedure with conditional logic

PostgreSQL: branching inside a procedure

SQL

CREATE PROCEDURE apply_late_fee(p_invoice_id INT)
LANGUAGE plpgsql
AS $$
DECLARE
  v_due_date DATE;
BEGIN
  SELECT due_date INTO v_due_date FROM invoices WHERE invoice_id = p_invoice_id;

  IF v_due_date < CURRENT_DATE THEN
    UPDATE invoices
    SET amount_due = amount_due * 1.05
    WHERE invoice_id = p_invoice_id;
  END IF;
END;
$$;

CALL apply_late_fee(1001);

Dialect	Define	Invoke	Procedural language
PostgreSQL	CREATE PROCEDURE ... LANGUAGE plpgsql	CALL procedure_name(...)	PL/pgSQL
SQL Server	CREATE PROCEDURE ... AS BEGIN ... END	EXEC procedure_name ...	T-SQL
Oracle	CREATE OR REPLACE PROCEDURE ... IS BEGIN ... END	BEGIN procedure_name(...); END;	PL/SQL
MySQL	CREATE PROCEDURE ... BEGIN ... END	CALL procedure_name(...)	SQL/PSM extensions

Syntax varies significantly by dialect

Unlike standard SELECT/INSERT/UPDATE/DELETE, procedural extensions are not well standardized across databases. PL/pgSQL (PostgreSQL), T-SQL (SQL Server), and PL/SQL (Oracle) each have their own control-flow syntax, variable declaration rules, and error-handling conventions. A stored procedure written for one database will not run unmodified on another — this is one of the more portability-limiting features in SQL, so check your specific database's documentation before writing anything nontrivial.

Stored procedures are not free of downsides: they move business logic out of version-controlled application code and into the database, which can make testing, code review, and deployment pipelines less straightforward unless the team has good tooling for versioning database objects. They are most valuable for logic that must run close to the data for performance or consistency reasons, or that needs to be shared across multiple applications written in different languages.

A stored procedure is a named, reusable, procedural block of SQL logic saved in the database.
CREATE PROCEDURE defines it; CALL (or EXEC in T-SQL) invokes it.
Benefits include reuse, fewer network round-trips, centralized logic, and finer-grained access control.
Procedural syntax is not standardized — PL/pgSQL, T-SQL, and PL/SQL each differ significantly.