SQL Stored Procedures Interview Questions and Answers

Stored procedures come up in nearly every SQL interview that touches backend or data-engineering work, because they force a conversation about transactions, error handling and where business logic should live. The syntax varies by database, so interviewers care more about the concepts — atomicity, parameters, and the architectural trade-off — than the exact CREATE PROCEDURE dialect.

The examples use accounts(id, balance) and transfers(id, from_id, to_id, amount). Because procedural SQL is dialect-specific, examples are labelled; the concepts are identical across engines.

What is a stored procedure and why use one?

A stored procedure is a named, precompiled block of SQL and procedural logic stored in the database and invoked with CALL/EXEC. It groups multiple statements into one server-side unit, which cuts network round trips and centralizes shared logic.

The core benefits to name: fewer round trips (one call runs many statements), a cached plan, centralized logic reused by many clients, and a security boundary — you can grant execute rights without granting direct table access.

-- PostgreSQL
CREATE PROCEDURE reset_balance(acct_id INT)
LANGUAGE SQL AS $$
  UPDATE accounts SET balance = 0 WHERE id = acct_id;
$$;
CALL reset_balance(7);

Q1. Procedure vs function — what is the real difference?

A function returns a value and is designed to be called inside a query; it generally may not change data. A procedure performs actions — DML, transaction control, multiple result sets — and is called on its own, not embedded in a SELECT.

-- Function: used inside a query, returns a value
CREATE FUNCTION balance_of(acct_id INT) RETURNS NUMERIC
LANGUAGE SQL AS $$ SELECT balance FROM accounts WHERE id = acct_id; $$;

SELECT id, balance_of(id) FROM accounts;   -- valid: function in a query

You cannot put a data-modifying procedure inside a SELECT. State the rule of thumb: "functions compute and return, procedures act and may commit." That framing answers the many follow-ups about where each is allowed.

Interview note: Follow-up: "can a function modify data?" In most engines no (Postgres allows it via PROCEDURE-like functions but discourages side effects in query context). The safe interview answer is functions are for computation.

Q2. Explain IN, OUT and INOUT parameters.

IN (default) passes a value in. OUT returns a value to the caller. INOUT does both — the caller provides a value the procedure can read and overwrite.

-- MySQL: OUT parameter returns the new balance
CREATE PROCEDURE deposit(IN acct_id INT, IN amt DECIMAL(10,2), OUT new_bal DECIMAL(10,2))
BEGIN
  UPDATE accounts SET balance = balance + amt WHERE id = acct_id;
  SELECT balance INTO new_bal FROM accounts WHERE id = acct_id;
END;

CALL deposit(7, 500.00, @b);
SELECT @b;   -- the OUT value

OUT parameters are how a procedure returns a scalar without producing a result set. Mention that returning a full result set (just running a SELECT at the end) is the other, more common way to hand data back.

Interview note: Trap: "what is an OUT parameter's value on entry?" Undefined/NULL — the procedure must assign it. Reading it before assignment is a bug.

Q3. How do you make a procedure atomic?

Wrap the steps in a transaction and commit only if all succeed; on any error, roll back so the database returns to its pre-call state.

-- PostgreSQL (PL/pgSQL)
CREATE PROCEDURE transfer(from_id INT, to_id INT, amt NUMERIC)
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE accounts SET balance = balance - amt WHERE id = from_id;
  UPDATE accounts SET balance = balance + amt WHERE id = to_id;
  -- both succeed or the whole procedure's transaction rolls back
END;
$$;

The money-transfer example is the canonical one: the debit and credit must both happen or neither does. Explain that partial success — debit committed, credit failed — is exactly what atomicity prevents, and that error handling is what triggers the rollback.

Interview note: Follow-up: "what about a check that the balance can't go negative?" Add a guard that raises an error when balance - amt < 0, which aborts and rolls back the transaction before any money moves.

Q4. How do you handle errors in a stored procedure?

Each dialect has an exception mechanism — DECLARE ... HANDLER in MySQL, BEGIN...EXCEPTION in PL/pgSQL, TRY...CATCH in SQL Server — used to catch failures, roll back and optionally re-raise or log.

-- SQL Server
CREATE PROCEDURE safe_transfer @from INT, @to INT, @amt MONEY AS
BEGIN
  BEGIN TRY
    BEGIN TRANSACTION;
      UPDATE accounts SET balance = balance - @amt WHERE id = @from;
      UPDATE accounts SET balance = balance + @amt WHERE id = @to;
    COMMIT;
  END TRY
  BEGIN CATCH
    ROLLBACK;
    THROW;   -- surface the error to the caller
  END CATCH
END;

The key point: catching an error without rolling back leaves a half-done transaction open — worse than not catching it. Always roll back in the handler, then decide whether to swallow or re-raise.

Interview note: Trap: "should you always suppress the error?" No — swallowing errors hides failures. Log and re-raise unless the caller genuinely can continue without knowing.

Q5. Are stored procedures actually faster?

They save network round trips (one call runs many statements) and reuse a cached execution plan, so repeated, chatty workloads benefit. They are not automatically faster than equivalent well-indexed application queries.

The honest answer distinguishes two effects: round-trip reduction is real and often significant for multi-statement operations; "precompilation" is a smaller, plan-caching effect that also applies to parameterized application queries. Do not claim procedures are magically fast.

-- One CALL replaces 5 separate app-to-DB round trips
CALL month_end_close(2026, 7);

Interview note: Follow-up: "can a cached plan hurt?" Yes — parameter sniffing can cache a plan optimized for one parameter value that's bad for others. It's a known SQL Server gotcha with mitigations like OPTION (RECOMPILE).

Q6. Can a stored procedure return multiple result sets?

Yes — a procedure can execute several SELECT statements, and each becomes a result set the caller reads in order. This is common for "fetch the order and its line items in one call".

-- MySQL: two result sets from one call
CREATE PROCEDURE order_detail(IN oid INT)
BEGIN
  SELECT * FROM orders WHERE id = oid;
  SELECT * FROM order_lines WHERE order_id = oid;
END;

Client drivers expose an API to advance through result sets (getMoreResults in JDBC). Mentioning that functions cannot do this — only procedures — reinforces the Q1 distinction.

Interview note: Follow-up: "how does the app read the second result set?" It iterates result sets via the driver; in JDBC that's Statement.getMoreResults(). Missing it means silently dropping data.

Q7. When should logic NOT go in a stored procedure?

When it is business logic that needs unit tests, version control, code review and independent deployment — those are easier in the application tier. Procedures are best for set-based, data-intensive operations that benefit from running next to the data.

The balanced interview answer: keep bulk data transformations, batch jobs and integrity-critical multi-step writes in SQL where atomicity is natural; keep evolving business rules in the application where the tooling is stronger. Avoid scattering logic across both layers, which makes behaviour hard to trace.

Interview note: Trap: "aren't procedures always faster so we should use them everywhere?" No — maintainability, testability and team scaling usually outweigh the round-trip savings for ordinary CRUD.

Q8. How do procedures affect security?

You can grant EXECUTE on a procedure without granting direct access to the underlying tables, so callers perform only the vetted operations the procedure exposes. This is a classic least-privilege pattern.

REVOKE ALL ON accounts FROM app_role;
GRANT EXECUTE ON PROCEDURE transfer TO app_role;  -- app can transfer, not read/alter freely

Combined with input validation inside the procedure, this reduces the SQL-injection surface too, because the app calls a fixed procedure rather than composing ad-hoc SQL. Naming least privilege plus reduced injection surface is the complete security answer.

Interview note: Follow-up: "do procedures fully prevent SQL injection?" No — dynamic SQL built inside a procedure with string concatenation is still injectable. Use parameterized dynamic SQL even inside procedures.

How to prepare

Write the money-transfer procedure with proper transaction control and a negative-balance guard, then deliberately make the second update fail and confirm the first is rolled back — that exercise teaches atomicity, error handling and the COMMIT/ROLLBACK placement all at once. Then rewrite the same logic in your application language and compare; being able to argue where the logic belongs is what senior interviewers are listening for.

Pair this with the triggers questions, since triggers are procedures the database fires automatically, and the views set for the other main server-side abstraction. The SQL learning path covers the transaction model these procedures rely on. A mock interview that walks through a multi-step write is the best rehearsal for the transaction-safety questions.