skip to content

How does a data-access layer invoke a server-side routine, and how do results come back to the caller?

level: middleimportance: nice to knowfreq 52%

answer

  1. not a query, a callable form
  2. parameter direction: in, out, in-out
  3. outbound types registered in advance
  4. consume result sets before reading outputs
  5. the layer cannot see what it changed

basics

~20 s

A layer calls a routine through a callable form declaring each parameter's direction, not through a plain query. Results arrive as result sets, as output parameter values, or as a returned value, consumed in that order.

solid answer

~40 s

A routine call is not a `SELECT`, so the ordinary query path does not fit it. Layers expose a **callable** form: you name the routine, declare each parameter's **direction** — in, out, or in-out — and bind values for the inbound ones before executing. Results come back in up to three ways at once: **result sets**, mapped exactly like any other native result to entities, transfer models or scalars; **output parameter values**, read after execution by name or position; and, for a function-shaped routine, a **single returned value**, which is often reachable through a plain query expression instead of a call. Several result sets must be consumed in order before the output parameters become readable. And whatever the routine did to rows, the layer did not see it.

go deeper

for a junior

Know that calling a server-side routine uses a different form from running a query, and that each parameter is declared as inbound, outbound, or both before the call executes.

for a middle

Explain the three result channels — result sets, output parameters, a function's return value — and the ordering rule that outputs are readable only after the rows have been consumed.

for a senior

Cover the operational edges: resources held open by the call, routines that control transactions internally, failures reported as status codes, and refreshing tracked state after a routine that wrote.

for a principal

Weigh an interface you cannot type-check against the reasons it exists — an execute-only permission model, a tuned batch, a legacy contract — and insist each call is wrapped once behind a typed operation.

## Why a call is not a query An ordinary statement has one shape: text in, one result set out. A server-side routine breaks that shape in three ways at once. It can take parameters that carry values **outward** as well as inward; it can produce **more than one** result set; and it can also **change rows** as a side effect while doing so. A data-access layer therefore exposes a distinct **callable** form rather than routing a call through the query path. (This is about *invoking* a routine from application code. Whether logic belongs in a routine at all, and how routines are written, are separate questions owned elsewhere.) ## Declaring the call The caller supplies, at minimum: 1. **The routine's name**, and in most systems the standard invocation form for a procedure — `CALL name(...)` — while a function-shaped routine is usually invoked inside an expression, for example `SELECT compute_total(?)`. 2. **A parameter list with directions.** Each parameter is declared `in`, `out`, or `in-out`. Inbound values are bound like any other parameter; outbound ones need a **type registered in advance**, because the layer has to know how to read the value back out of the call. This registration step is the part people forget, and it fails at execution rather than at build time. 3. **A result-set mapping**, if the routine produces rows and you want them typed. The same three targets apply as for any native statement: a mapped entity type, a transfer model, or scalars. ## How results arrive | Channel | What it carries | How the caller reads it | |---|---|---| | **Result set** | rows, one or more sets | iterate and map, set by set, in order | | **Output parameter** | scalar values, counts, status codes | read by name or position after execution | | **Return value** | one value from a function-shaped routine | usually consumed as an ordinary query expression | | **Row count** | how many rows a write affected | reported by the execution itself | The ordering rule matters and surprises people: when a routine yields both result sets and output parameters, the **output values are generally not readable until the result sets have been consumed**. Reading an output parameter first can return nothing, a stale value, or an error depending on the layer and driver. Consume rows first, then read outputs, then close the call. A routine returning multiple result sets also has to be advanced explicitly — the caller asks for the next set until there are none, and each set may have a different shape and therefore a different mapping. ## Resources and boundaries - **A call holds resources open.** Its result sets, like any cursor, are valid only while the call and its transaction are open; materialise what you need before leaving that scope. - **A call joins the current transaction** the way any other statement does, but a routine may perform its own transaction control internally where the engine allows it. That interacts badly with a caller that assumes it alone decides when work commits, so it is worth knowing whether the routines you call do this. - **Errors surface differently.** A routine can signal failure by raising, by an output status code, or by returning zero rows. Only the first of these is an exception to your code, so a status-code convention needs to be checked explicitly or failures pass silently. ## The effect on tracked state The layer sees a call it cannot interpret. If the routine modified rows, then: - objects already loaded for those rows still hold their **pre-call values**; - any cache the layer keeps beyond the current unit of work may also hold pre-call values; - the layer's own pending changes, if not yet sent, were **not visible to the routine** while it ran. So a call that writes is bracketed by the same discipline as any raw write: get pending work to the database first, then call, then discard or refresh the tracked copies of anything the routine touched. A read-only routine needs only the first half of that. ## Practical judgement Routine calls are worth using where they already exist — a legacy interface, a permission boundary where the application is granted only routine execution, or a batch operation someone has already written and tuned. They cost you the layer's checking twice over: the routine's name and signature are strings, its result shapes are not declared anywhere your build can see, and a change to either is discovered when the call runs. Wrap each call in exactly one place, map its result sets to explicit types there, and let the rest of the codebase see an ordinary typed operation.

  • A routine returns rows and a status output parameter, and the status always reads empty. What is the likely cause?
    The output parameter is being read before the result sets have been consumed. Output values are typically populated only once the call's row results are exhausted. Iterate every result set, advancing through them all, then read the outputs, then close the call.
  • Why is calling a routine harder to keep safe under refactoring than an object-level query?
    The routine name, its parameter directions and its result-set shapes are all strings and conventions the build cannot check. The routine also lives outside the application's source, so its signature can change independently. Only a test that actually executes the call will catch a mismatch.

saying these in an interview costs you the question

  • Treats a routine call as an ordinary query with no parameter directions
  • Reads output parameters before consuming the routine's result sets
  • Forgets that outbound parameter types must be registered before execution
  • Assumes a routine that writes leaves loaded objects up to date
  • Ignores a status-code failure convention because nothing was thrown