Using Firebird Stored Procedures to Cut Network Traffic and Encapsulate Logic

Problem: Repeated SQL Logic Causes Network Overhead

When an application repeatedly sends the same SELECT‑INSERT‑UPDATE pattern to Firebird, each round‑trip adds latency and consumes bandwidth.

Thesis: Server‑side stored procedures in Firebird (PSQL) let you move that logic onto the database, cut network traffic, and keep the code in one place.

How Firebird Stored Procedures Work

Firebird’s procedural language (PSQL) extends plain SQL with variables, loops, conditionals, and exception handling. A procedure is created once, compiled, and stored in the database metadata.

Creating a Procedure that Returns Multiple Result Sets

CREATE PROCEDURE GET_ORDER_SUMMARY (
    START_DATE DATE,
    END_DATE   DATE
)
RETURNS (
    ORDER_ID   INTEGER,
    CUST_NAME  VARCHAR(100),
    TOTAL_AMT  DECIMAL(15,2)
)
AS
DECLARE VARIABLE CUR_ORDER CURSOR FOR
    SELECT O.ID, C.NAME, SUM(L.PRICE * L.QTY)
    FROM ORDERS O
    JOIN CUSTOMERS C ON O.CUST_ID = C.ID
    JOIN ORDER_LINES L ON L.ORDER_ID = O.ID
    WHERE O.ORDER_DATE BETWEEN :START_DATE AND :END_DATE
    GROUP BY O.ID, C.NAME;
BEGIN
    OPEN CUR_ORDER;
    WHILE (1=1) DO
    BEGIN
        FETCH CUR_ORDER INTO :ORDER_ID, :CUST_NAME, :TOTAL_AMT;
        IF (ROW_COUNT = 0) THEN LEAVE;
        SUSPEND;   -- returns a row to the caller
    END
    CLOSE CUR_ORDER;
END
^

The SUSPEND statement makes the procedure return a row each time it is called, allowing the client to fetch multiple rows as a result set.

Calling the Procedure from an Application

From any Firebird client (e.g., isql, JDBC, .NET provider) you use EXECUTE PROCEDURE:

EXECUTE PROCEDURE GET_ORDER_SUMMARY ('2026-01-01', '2026-01-31');

The driver receives the result set just like a regular SELECT.

Worked Example: Reducing Round‑Trips

Suppose a reporting tool needs order totals for a month and then, for each order, the list of line items. Without a procedure, the tool would:

1. Query orders for the month (one round‑trip).
2. For each order, query its line items (N round‑trips).

With a stored procedure that returns both the order summary and, via a second cursor, the line‑item details, the client makes a single call and receives two result sets:

CREATE PROCEDURE GET_ORDER_DETAILS (
    START_DATE DATE,
    END_DATE   DATE
)
RETURNS (
    ORDER_ID   INTEGER,
    ORDER_DATE DATE,
    CUST_NAME  VARCHAR(100),
    LINE_ID    INTEGER,
    PRODUCT    VARCHAR(50),
    QTY        INTEGER,
    PRICE      DECIMAL(10,2)
)
AS
DECLARE VARIABLE CUR_ORDER CURSOR FOR
    SELECT O.ID, O.ORDER_DATE, C.NAME
    FROM ORDERS O
    JOIN CUSTOMERS C ON O.CUST_ID = C.ID
    WHERE O.ORDER_DATE BETWEEN :START_DATE AND :END_DATE;
DECLARE VARIABLE CUR_LINE CURSOR FOR
    SELECT L.ID, P.NAME, L.QTY, L.PRICE
    FROM ORDER_LINES L
    JOIN PRODUCTS P ON L.PROD_ID = P.ID
    WHERE L.ORDER_ID = :ORDER_ID;
BEGIN
    OPEN CUR_ORDER;
    WHILE (1=1) DO
    BEGIN
        FETCH CUR_ORDER INTO :ORDER_ID, :ORDER_DATE, :CUST_NAME;
        IF (ROW_COUNT = 0) THEN LEAVE;
        OPEN CUR_LINE;
        WHILE (1=1) DO
        BEGIN
            FETCH CUR_LINE INTO :LINE_ID, :PRODUCT, :QTY, :PRICE;
            IF (ROW_COUNT = 0) THEN LEAVE;
            SUSPEND;
        END
        CLOSE CUR_LINE;
    END
    CLOSE CUR_ORDER;
END
^

Calling EXECUTE PROCEDURE GET_ORDER_DETAILS ('2026-01-01','2026-01-31'); returns a combined stream where each row contains both order and line data, eliminating the per‑order query loop.

Trade‑offs and Limitations

• Version differences: Syntax for packages and overloads changed between Firebird 2.5, 3.0, and 4.0. The examples above work unchanged in 3.0+; for 2.5 you may need to avoid SUSPEND in certain contexts.
• Debugging: Firebird offers limited debugging tools; you must rely on EXCEPTION blocks and logging to monitor errors.
• Security: Procedures run with the privileges of their owner. Grant the procedure to specific roles and avoid embedding privileged DDL inside a procedure unless necessary.

Actionable Checklist

1. Verify your Firebird version: SELECT VERSION FROM MON$VERSION;
2. Create the procedure using isql or your admin tool, committing with COMMIT;
3. Test the call: EXECUTE PROCEDURE GET_ORDER_SUMMARY ('2026-01-01','2026-01-31'); and confirm you receive rows.
4. Measure network traffic (e.g., with Wireshark or client statistics) before and after introducing the procedure to see the reduction in round‑trips.
5. Add exception handling if needed: WHEN ANY DO EXCEPTION EXCEPTION_VALUE 'Unexpected error';

By moving repetitive SQL logic into a Firebird stored procedure, you keep the code centralized, lower latency, and simplify client code—while staying aware of version‑specific syntax and privilege considerations.