skip to content

A stored procedure needs to hand several values and a set of rows back to the calling application. What mechanisms does a relational database offer for that, and what are their tradeoffs?

level: middleimportance: should knowfreq 42%

answer

  1. OUT/INOUT for scalars, catalog-declared
  2. result sets: implicit, undeclared shape, can't be joined
  3. ref-cursor handle = explicit, streamable
  4. temp table = pipeline handoff, same session only
  5. want to join it? make it a table function

basics

~20 s

Scalars come back through OUT or INOUT parameters. Row sets come back either as a result set the procedure emits to the client, as a returned cursor handle the caller iterates, or by writing to a temporary table the caller then queries. Functions instead return a value or a table directly.

solid answer

~1 min

Four mechanisms in practice: 1. **OUT / INOUT parameters** — best for a handful of scalars (new id, row count, status). Strongly typed, discoverable in the catalog, mapped by every driver via `CallableStatement`-style registration. INOUT means the caller passes a value in and reads a modified one back. 2. **Result sets** — the procedure runs queries whose rows flow straight to the client; drivers expose one or more result sets from a single CALL. Convenient, but the shape is not declared in the catalog, so callers cannot statically know the columns, and multiple result sets are awkward in many ORMs. 3. **Cursor / ref-cursor out parameter** — the procedure opens a cursor and returns the handle; the caller fetches from it. Explicit and streamable, and it is the common route in engines where procedures do not emit implicit result sets. 4. **Shared temporary table** — the procedure fills a session-scoped temp table, the caller selects from it. Useful for multi-step ETL, but it couples the caller to a table name and to the same session. If the result really is *a table of rows derived from inputs*, prefer a table-valued/set-returning **function** — it composes into queries, which a procedure's result set never can.

code

sql · 20 lines
sql
CREATE PROCEDURE place_order(IN p_customer bigint,
                            OUT p_order_id bigint,
                            OUT p_line_count int)
AS $$
BEGIN
  INSERT INTO orders(customer_id) VALUES (p_customer) RETURNING id INTO p_order_id;
  SELECT count(*) INTO p_line_count FROM order_line WHERE order_id = p_order_id;
END;
$$;

CREATE FUNCTION open_orders_for(p_customer bigint)
RETURNS TABLE (order_id bigint, total numeric)
AS $$
  SELECT o.id, sum(l.qty * l.unit_price)
  FROM orders o JOIN order_line l ON l.order_id = o.id
  WHERE o.customer_id = p_customer AND o.status = 'OPEN'
  GROUP BY o.id
$$ LANGUAGE sql STABLE;

SELECT * FROM open_orders_for(42) WHERE total > 100;

go deeper

for a junior

Know that OUT parameters exist and that in some engines a SELECT inside a procedure sends rows straight to the client.

for a middle

Compare the mechanisms and their driver-side handling, and note that result-set shapes are not declared in the catalog while parameter signatures are.

for a senior

Make the composability point — a CALL's output cannot be joined — and treat cursors and temp tables as deliberate choices for streaming and pipeline handoff respectively.

for a principal

Discuss the routine library as a versioned API: declared signatures, one error convention, and avoiding undeclared contracts such as shared temp-table names.

## Why this is not obvious A function has one exit door: its return value. A procedure has no return type at all, so getting data out of it is a design choice, and engines differ enough that candidates who have only used one product often assume their route is the only one. ## OUT and INOUT parameters Parameters have a mode: `IN` (default, caller supplies), `OUT` (procedure assigns, caller reads afterwards), `INOUT` (both). This is the cleanest channel for a small, fixed set of scalars — a generated primary key, an affected-row count, an error code, a computed total. What makes them good: the signature is in the catalog, so tools and drivers know the names and types; there is no ambiguity about how many values come back; and the client API is uniform (register output parameters, call, read them). What makes them limited: they carry scalars, not sets, and a procedure with eight OUT parameters is a design smell — that is a row, and it wants to be a row type or a result set. Some engines let an OUT parameter carry a composite/row type or an array, which stretches the mechanism a little further before you need something else. ## Result sets In SQL Server and MySQL, a bare `SELECT` inside a procedure body streams its rows to the client. A single CALL can therefore produce several result sets in sequence, and the driver walks them (`getMoreResults()` in JDBC, `nextset()` in Python DB-APIs). The appeal is obvious: no plumbing. The costs are real. The column list is not declared anywhere in the catalog, so nothing can validate it at compile time and any body change silently reshapes the client contract. Multiple result sets are poorly supported by ORMs and by tools that expect one shape per call. And crucially, a procedure's result set **cannot be consumed by SQL** — you cannot join to a CALL. Whenever the caller might want to filter, join, or aggregate the output, that is the signal to write a set-returning function instead. ## Cursors as output PostgreSQL and Oracle lean on cursor handles: the procedure opens a cursor over a query and returns its name or ref-cursor in an OUT parameter; the caller then fetches from it, optionally in batches. This is explicit, streams naturally for large results, and lets the caller decide fetch size, at the cost of ceremony and a live cursor whose lifetime is tied to the transaction (or to `WITH HOLD`). ## Temporary tables as a channel A procedure can materialize its output into a session-scoped temporary table and let the caller query it. This is standard practice in multi-step ETL, where step 2 wants step 1's output as a *table* it can index and join, not as a stream. The tradeoffs: the caller must know the table name (an undeclared contract), the two must share a session — which connection pooling can break — and the materialization costs writes. ## Functions returning tables When the output is a set of rows that is a pure derivation of the inputs, a **table-valued / set-returning function** is usually the better tool: `SELECT * FROM active_orders_for(42) WHERE total > 100` composes, whereas a CALL does not. Its result shape *is* declared in the catalog, so callers and tools can see it. The considerations specific to those functions — how the optimizer estimates their cardinality, whether they inline — are their own topic. ## Error and status conventions A related question is how failure comes back. Two idioms compete: raise an exception and let it propagate (the caller's driver turns it into an error), or return a status code in an OUT parameter and keep the call "successful". Exceptions are generally preferable because they cannot be ignored and they interact correctly with transaction rollback; status codes are used where partial success is a genuine outcome and the caller must decide what to do. Mixing both inconsistently across a routine library is a frequent source of bugs — pick one convention. ## What a good answer sounds like Name OUT/INOUT parameters for scalars, result sets or cursors for row sets, note the temp-table channel for pipeline steps, and close with the discriminator: if the caller wants to *query* the output, it belongs in a function, because SQL can only compose with things that appear in a FROM clause.

  • Why can't a caller join to the result set produced by a CALL?
    Because a result set is a client-protocol artifact, not a relation in the query's namespace — the rows are streamed to the driver rather than being available to the optimizer as a FROM item. SQL can only compose over things that can appear in a FROM clause: tables, views, subqueries, and table functions. If composability is needed, the routine must be a set-returning function, or it must land its rows in a table the caller can then query.

saying these in an interview costs you the question

  • Believing procedures cannot return data at all.
  • Assuming implicit result sets exist in every engine — PostgreSQL and Oracle procedures do not emit them the way SQL Server and MySQL do.
  • Designing a procedure with a long list of OUT parameters instead of returning a row or table type.
  • Passing results via a temp table without noticing the caller must share the same session.
  • Returning error codes in OUT parameters while also raising exceptions elsewhere, with no consistent convention.

context

resolving links…