What is a stored procedure? | Interview Question

Q: What is a stored procedure?

**Target Audience:** Beginners who have run single SQL statements and hear "put it in a stored procedure"; candidates who need to discuss procedure vs function and the real trade-offs. **Difficulty:** Medium **Engines:** PostgreSQL 16.15 (all procedure and function examples, error messages and permission test) and SQLite 3.45.0 through sql.js 1.10.2 (the runnable example). SQLite has no stored procedures; MySQL and SQL Server are described, not run. > **How to read this doc:** Each idea comes in plain words first, then a `📌 Interview term:` callout gives the precise vocabulary. Every output and error message here comes from an actual run; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First You call your bank and ask to move 30 from your account to a friend's. One way: the call-centre agent reads you each step and waits for you to confirm — "subtract 30 from account 1?" "yes" "add 30 to account 3?" "yes" "write it in the log?" "yes

The problem

Question presented to candidate: "What is a stored procedure? How is it different from a function, and when would you put logic in the database instead of in the application?"

What a strong answer should cover:

A stored procedure is named code stored in the database (SQL plus control flow such as variables, IF, loops, error handling) that clients run with one call, CALL transfer(1, 3, 30)
Procedure vs function in PostgreSQL: a function returns a value and is used inside queries (SELECT ... FROM f()); a procedure is invoked with CALL, returns results only through INOUT parameters, and (since PostgreSQL 11) may COMMIT / ROLLBACK inside
Benefits: one round trip for a multi-step operation, logic and invariants enforced next to the data for every client, permission control with GRANT EXECUTE and SECURITY DEFINER
Costs: harder to version, test, debug and deploy than application code; vendor lock-in (PL/pgSQL, T-SQL, MySQL syntax all differ); database CPU is the hardest tier to scale
Correct the myth: "precompiled" is not a unique advantage; PL/pgSQL caches statement plans per session, and prepared statements from the application get plan reuse too; SQLite has no stored procedures at all

Code / implementation expected: Optional — a small CREATE PROCEDURE with parameters and a CALL, and the difference from a function.

Schema used by the examples:

stored-procedure

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who have run single SQL statements and hear "put it in a stored procedure"; candidates who need to discuss procedure vs function and the real trade-offs. Difficulty: Medium Engines: PostgreSQL 16.15 (all procedure and function examples, error messages and permission test) and SQLite 3.45.0 through sql.js 1.10.2 (the runnable example). SQLite has no stored procedures; MySQL and SQL Server are described, not run.

How to read this doc: Each idea comes in plain words first, then a 📌 Interview term: callout gives the precise vocabulary. Every output and error message here comes from an actual run; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

You call your bank and ask to move 30 from your account to a friend's. One way: the call-centre agent reads you each step and waits for you to co

The solution is hidden so you can try first

13 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

PostgreSQL: a transfer procedure, called twice with CALL

CREATE PROCEDURE transfer(p_from INTEGER, p_to INTEGER, p_amount INTEGER)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE accounts SET balance = balance - p_amount
  WHERE id = p_from AND balance >= p_amount;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'account % cannot pay %', p_from, p_amount;
  END IF;

  UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;

  INSERT INTO transfers (id, from_id, to_id, amount)
  VALUES ((SELECT COALESCE(MAX(id), 0) + 1 FROM transfers), p_from, p_to, p_amount);
END;
$$;

CALL transfer(1, 3, 30);   -- Asha pays Chen 30
CALL transfer(4, 2, 120);  -- Dara pays Ben 120

SELECT a.id, a.owner, a.balance,
       (SELECT COUNT(*) FROM transfers t WHERE a.id IN (t.from_id, t.to_id)) AS transfers
FROM accounts a
ORDER BY a.id;

PostgreSQL: a function returns rows, so it can be used inside a query

-- A function returns a value, so it can sit inside a query
CREATE FUNCTION rich_accounts(p_min INTEGER)
RETURNS TABLE (owner TEXT, balance INTEGER)
LANGUAGE sql STABLE
AS $$
  SELECT owner, balance FROM accounts WHERE balance >= p_min
$$;

SELECT owner, balance, balance >= 100 AS is_vip
FROM rich_accounts(75)
ORDER BY balance DESC;

PostgreSQL: a procedure returns values through INOUT parameters

-- A procedure hands results back through INOUT parameters: CALL returns them as one row
CREATE PROCEDURE total_balance(INOUT p_total INTEGER DEFAULT NULL, INOUT p_accounts INTEGER DEFAULT NULL)
LANGUAGE plpgsql
AS $$
BEGIN
  SELECT SUM(balance), COUNT(*) INTO p_total, p_accounts FROM accounts;
END;
$$;

CALL total_balance();

SQLSQLite (no procedures): the same transfer sent by the application in one transaction

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.