Firebird SQL access parameter by name in stored procedure

Question

Is there a way to access an output parameter by name in a Firebird (v1.5) stored procedure? The result fields should be the month of a specified year and it would be easier if I don't have to copy my code 12 times.

Here is an example (the final code is more complex than this):

SET TERM !!;

CREATE PROCEDURE MY_PROC (
  I_PARAM INTEGER)
RETURNS (
  O_PARAM_1 INTEGER, O_PARAM_2 INTEGER, O_PARAM_3 INTEGER, O_PARAM_4 INTEGER,
  O_PARAM_5 INTEGER, O_PARAM_6 INTEGER, O_PARAM_7 INTEGER, O_PARAM_8 INTEGER,
  O_PARAM_9 INTEGER, O_PARAM_10 INTEGER, O_PARAM_11 INTEGER, O_PARAM_12 INTEGER)
AS
  declare MONTH_ID INTEGER;
BEGIN
  MONTH_ID = 1;

  while (MONTH_ID <= 12) do
  begin
    select
      count(*)
    from
      MY_TABLE M
    where
      M.MONTH_ID = I
    into
>>>>  :O_PARAM_???; -- need access by name

    MONTH_ID = MONTH_ID + 1;
  end

  suspend;

END!!

SET TERM ;!!

Mark Rotteveel · Accepted Answer · 2015-05-04T09:59:42.860

This isn't possible. Firebird needs to know at compile time which variable it is going to use. Dynamically referencing the output parameter is not possible.

As an intermediate solution, you could assign the result to a temporary variable, and then only do the assignment to the right variable in a long chain of if-else. It is still code duplication but less than repeating the query over and over.

You could also execute a single query that produces the results for all months at once. This would lead to a slight code bloat, but probably be more efficient:

select 
  (SELECT count(*) from MY_TABLE where MONTH_ID = 1),
  (SELECT count(*) from MY_TABLE where MONTH_ID = 2),
  (SELECT count(*) from MY_TABLE where MONTH_ID = 3),
  -- ...and so on
into :O_PARAM_1, :O_PARAM_2, :O_PARAM_3;

If you used a more recent Firebird version like Firebird 2.5, you could use a CTE (although for this simple query it doesn't simplify it a lot):

WITH counts AS (SELECT MONTH_ID, count(*) AS MONTH_COUNT from MY_TABLE M GROUP BY MONTH_ID)
select 
  (SELECT MONTH_COUNT from counts where MONTH_ID = 1),
  (SELECT MONTH_COUNT from counts where MONTH_ID = 2),
  (SELECT MONTH_COUNT from counts where MONTH_ID = 3),
  -- ...and so on
into :O_PARAM_1, :O_PARAM_2, :O_PARAM_3;

A totally different solution would be to abandon the idea of an executable stored procedure (although with the presence of SUSPEND in your current solution it is actually selectable with always one row), and instead use a selectable procedure that returns the result as rows. If that is a solution depends on your actual data needs.

CREATE PROCEDURE MY_PROC (
  I_PARAM INTEGER)
RETURNS (
  MONTH_ID INTEGER, MONTH_COUNT INTEGER)
AS
BEGIN
  FOR
    select MONTH_ID, count(*)
    from MY_TABLE M
    GROUP BY MONTH_ID
    into :MONTH_ID, :MONTH_COUNT
  BEGIN
    SUSPEND;
  END
END

Firebird SQL access parameter by name in stored procedure

1 Answers1