Firebird Selectable Stored Procedures: When a Parameterized View Beats a Plain Query

You need a report query: open orders for a customer, with derived columns like days overdue, filtered by rules too tangled for a single WHERE clause. A view can't take parameters. A plain SELECT with everything inlined becomes unreadable. Firebird has a middle option that fits this exact shape: the selectable stored procedure — a procedure you call with SELECT * FROM, like a view with arguments.

The thesis of this post: selectable procedures are the right tool when you want to encapsulate parameterized, procedural result-set logic inside the database and expose it through a clean, grantable interface — and the wrong tool when the query is simple enough that the optimizer should see all of it.

What a selectable procedure actually is

A Firebird stored procedure becomes "selectable" when two things are true: it declares output columns with RETURNS (...), and its body emits rows with the SUSPEND statement. SUSPEND pauses execution and yields one row to the caller; when the caller asks for the next row, execution resumes. The result is a virtual stream the caller can filter, join, and sort in an outer SELECT — a parameterized view, in effect.

Unlike a view, the body is full PSQL (Firebird's procedural SQL): variables, loops, conditionals, even calls to other procedures. That makes it a natural home for report logic that would otherwise live in application code or in a monstrous derived-table query.

A worked example: open orders with derived columns

Assume tables ORDERS(order_id, customer_id, order_date, due_date, status) and CUSTOMERS(customer_id, name). Create this in a scratch database using isql (or a GUI like FlameRobin) with a user that has CREATE PROCEDURE rights:

CREATE PROCEDURE GetOpenOrders (
    p_customer_id INTEGER,
    p_min_date    DATE)
RETURNS (
    order_id      INTEGER,
    customer_name VARCHAR(80),
    due_date      DATE,
    days_overdue  INTEGER)
AS
BEGIN
  FOR
    SELECT o.order_id, c.name, o.due_date
    FROM orders o
    JOIN customers c ON c.customer_id = o.customer_id
    WHERE o.customer_id = :p_customer_id
      AND o.status = 'OPEN'
      AND o.order_date >= :p_min_date
    ORDER BY o.due_date
    INTO :order_id, :customer_name, :due_date
  DO
  BEGIN
    days_overdue =
      CASE WHEN due_date < CURRENT_DATE
           THEN CURRENT_DATE - due_date
           ELSE 0 END;
    SUSPEND;
  END
END

Callers then use it like a table:

SELECT * FROM GetOpenOrders(42, DATE '2026-01-01')
WHERE days_overdue > 30
ORDER BY due_date;

To verify it works, run the SELECT in isql and confirm you get rows with the computed days_overdue column, then try a customer ID with no open orders and confirm you get an empty result rather than an error. Note the exact PSQL syntax above is valid on the Firebird 2.5 through 5.x lines, but always check your version's Language Reference — available functions and statement syntax differ between releases.

The privilege angle: curated access without table grants

A genuinely useful side effect: Firebird lets you GRANT SELECT ON PROCEDURE GetOpenOrders TO ROLE report_reader;. A reporting user can then call the procedure without any rights on ORDERS or CUSTOMERS (the procedure itself needs the underlying rights, typically granted to the procedure). This gives you a curated, parameterized data-access layer — closer to an API than to raw table exposure — using nothing but built-in mechanisms.

The trade-off: the optimizer sees a black box

Here's the limitation that decides whether this pattern is right for you. To Firebird's optimizer, a selectable procedure is opaque. Predicates in the outer query — like WHERE days_overdue > 30 above — are not pushed into the procedure body. The procedure produces its full intermediate rowset row by row, and the outer query filters afterward. With an equivalent plain SELECT, the optimizer could instead use indexes to satisfy the same predicates directly.

Practical consequences:

• Put every filter you can into the procedure's parameters, not the outer WHERE clause. Parameters are the only "pushdown" you get.
• For simple lookups, a plain query or view is better: cheaper to plan, easier to read the execution plan for, and less code to maintain.
• Before committing to the pattern on real data, compare costs. In isql, run SET PLAN; and SET STATS;, then execute the procedure call and the equivalent direct SELECT with the same filters, and compare fetches and reads at realistic volume. Don't assume — measure.

Deciding, and checking your decision

Reach for a selectable procedure when the logic is genuinely procedural (derived values, conditional row shaping, multi-step assembly), when callers need parameters, or when you want grant-based curated access. Reach for a view or direct query when the logic is a straightforward join-and-filter and the optimizer should see everything.

The actionable part: prototype the procedure in a scratch database, confirm behavior with a couple of parameter combinations, then run the SET PLAN/SET STATS comparison against the direct-SQL alternative on data that resembles production volume. If the procedure's cost is within your budget, you get encapsulation and a clean privilege boundary; if it isn't, you've learned that with a cheap experiment rather than a slow report in production.