Stored Procedures and Functions Concepts

A stored procedure (or function) is a named block of SQL and procedural logic that is saved inside the database itself and can be executed by calling its name, rather than sending the full SQL text from the application every time. Most major databases support them, though the exact syntax varies — PostgreSQL uses PL/pgSQL, SQL Server uses T-SQL, and Oracle uses PL/SQL.

Stored procedures and functions bring logic closer to the data, which can reduce network round trips and let the database enforce rules consistently no matter which application or tool is writing to it.

Why it matters

Stored procedures and functions put logic inside the database, where it runs next to the data. Banks and large enterprises use them heavily, so you are likely to read and maintain them. They can cut network round trips and centralise rules, at the cost of logic that is harder to version and test than application code.

CREATE PROCEDURE and CREATE FUNCTION

CREATE PROCEDURE defines a named routine that performs an action — it can run multiple statements, including transactions, and is invoked with CALL procedure_name(...). CREATE FUNCTION defines a named routine that computes and returns a value, and can typically be used directly inside a SQL expression, such as in a SELECT list or WHERE clause. The key practical difference: a function must return a value and is used as part of a larger query, while a procedure is invoked on its own and does not have to return anything.

When Server-Side Logic Makes Sense

Stored procedures are a good fit when: the same multi-step logic needs to run identically from several different applications or tools, and you want to guarantee consistency rather than reimplementing it in each one; the operation involves many round trips between the application and database that could instead happen in one call, reducing network latency; or you need to enforce a business rule at the database level so it applies no matter what connects to the database. They are usually a poor fit when the logic changes frequently (stored procedures are harder to version-control and deploy than application code), when the team's tooling and expertise are centered on the application layer, or when portability across different database vendors matters, since procedural SQL dialects are not standardized.

• Good fit: shared logic across multiple applications, reducing round trips, enforcing rules centrally.
• Poor fit: rapidly changing logic, teams without strong database tooling, needing to stay portable across database vendors.

Examples

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

CALL transfer_funds(1, 2, 100.00);

CREATE PROCEDURE
CALL
(Both balance updates run as one server-side unit; any application that calls
transfer_funds gets the same guaranteed behavior without duplicating the logic.)

CREATE FUNCTION order_total(order_id INT)
RETURNS DECIMAL
LANGUAGE plpgsql
AS $$
DECLARE
    total DECIMAL;
BEGIN
    SELECT SUM(price * quantity) INTO total
    FROM order_items
    WHERE order_items.order_id = order_id;
    RETURN total;
END;
$$;

SELECT id, order_total(id) AS total FROM orders WHERE id = 501;

id: 501 | total: 149.99

Exercise

This exercise uses PostgreSQL's function syntax, so it cannot run in the playground. Write a function order_total that takes an order id and returns the total value of that order, calculated as the sum of quantity multiplied by unit price over its rows in order_items. Then call it for order 1.

Expected result: the SELECT returns one row with the total for order 1.

-- order_items(order_id, quantity, unit_price)

-- TODO: create the function order_total(p_order_id) returning a numeric total

-- TODO: call it for order 1

-- order_items(order_id, quantity, unit_price)

CREATE FUNCTION order_total(p_order_id INTEGER)
RETURNS NUMERIC
LANGUAGE sql
AS $$
  SELECT COALESCE(SUM(quantity * unit_price), 0)
  FROM order_items
  WHERE order_id = p_order_id;
$$;

SELECT order_total(1);

Quick quiz

Interview questions