Variable number of parameters in a Firebird stored procedure

Question

I have the following stored procedure:

ALTER PROCEDURE SP_STOCK_ANALYSIS 
(
  MAIN_GROUP CHAR(6)
)
RETURNS
(
  STOCK_CODE CHAR(21),
  STOCK_GROUP CHAR(6),
  DESCRIPTION CHAR(31),
  EXPENSE NUMERIC(15, 4)
)
AS

BEGIN
   FOR
      SELECT
         L.STOCK_CODE, INV.DESCRIPTION, INV.STOCK_GROUP, L.BALANCE
      FROM
         LEDGER L LEFT JOIN INVENTORY INV ON L.STOCK_CODE = INV.STOCK_CODE
      WHERE
         INV.STOCK_GROUP in (:MAIN_GROUP)
         AND L.LEDGER_ACCOUNT in ('71212', '71211' ,'83791')
      INTO
         STOCK_CODE, STOCK_GROUP, DESCRIPTION, EXPENSE
   DO

In the select statement I have the following three accounts:

• 71212
• 71211
• 83791

Ideally I would like to change the stored procedure so that I am able to enter the account numbers as part of the parameters. The challenge is that the number of accounts might change. Is it possible to use a string as a parameter? How would I go about doing that?

Answer 1

Firebird does not support a variable number of parameters to a stored procedure. However, you can define default parameter values. So, you could specify a first parameter without default, followed by multiple parameters with a default, and then call the stored procedure with 1 or more parameters.

create procedure SP_STOCK_ANALYSIS (
    group_1 CHAR(6), group_2 CHAR(6) DEFAULT NULL, group_3 CHAR(6) DEFAULT NULL /* ... etc ...*/)
  RETURNS (
    STOCK_CODE CHAR(21),
    STOCK_GROUP CHAR(6),
    DESCRIPTION CHAR(31),
    EXPENSE NUMERIC(15, 4))
as
begin
  for select L.STOCK_CODE /* ... etc ... */
    from LEDGER L LEFT JOIN INVENTORY INV ON L.STOCK_CODE = INV.STOCK_CODE
    where INV.STOCK_GROUP in (group_1, group_2, group_3 /* ... etc ... */)
    /* ... etc ... */
    into STOCK_CODE /* ... etc ... */
  do
  begin
    /* ... etc ... */
  end
end

Alternatively, you could pass a comma separated string, and use a helper stored procedure to split that string into multiple rows.

You would then do something like

create procedure SP_STOCK_ANALYSIS(group_list VARCHAR(8191)
  RETURNS (
    STOCK_CODE CHAR(21),
    STOCK_GROUP CHAR(6),
    DESCRIPTION CHAR(31),
    EXPENSE NUMERIC(15, 4))
as
begin
  for select L.STOCK_CODE /* ... etc ... */
    from LEDGER L LEFT JOIN INVENTORY INV ON L.STOCK_CODE = INV.STOCK_CODE
    where INV.STOCK_GROUP in (select group_value from split_groups(:group_list))
    /* ... etc ... */
    into STOCK_CODE /* ... etc ... */
  do
  begin
    /* ... etc ... */
  end
end

With split_groups something like

create procedure split_group(group_list varchar(8191))
  returns (group_value varchar(1000))
as
  declare previouspos smallint = 1;
  declare nextpos smallint;
begin
  -- produce no rows for null input
  if (group_list is null) then exit;
  -- find next , in group_list
  nextpos = position(',', group_list);
  while (nextpos <> 0) do
  begin
    -- get item
    group_value = substring(group_list from previouspos for nextpos - previouspos);
    if (char_length(group_value) > 0) then
      -- output item as a row
      suspend;
    -- first character after the found ,
    previouspos = nextpos + 1;
    -- find next , in group_list
    nextpos = position(',', group_list, previouspos);
  end
  -- output item after last found ,
  group_value = substring(group_list from previouspos);
  if (char_length(group_value) > 0) then
    suspend;
end

Answer 2

You also have an option of using reversed LIKE instead of IN. Afterall, IN can get slower on Interbase/Firebird, as the number of items grow. LIKE would always do natural scan of the whole table. Which is much slower if the number of items is small, but will not slow down as the number of item grows.

Make your own profiling. You can even switch to one or another strategy, based on parameter string length. You was puzzled with 32KB Firebird VarChar length limit, maybe it can matter for your application.

So, for general orientation see my answer at https://stackoverflow.com/a/43997801/976391

To apply "Route #2" to your case would be something like this...

ALTER PROCEDURE SP_STOCK_ANALYSIS 
(
  MAIN_GROUP varCHAR(32760) character set ascii
)
RETURNS
(
  STOCK_CODE varCHAR(21),
  STOCK_GROUP varCHAR(6),
  DESCRIPTION varCHAR(31),
  EXPENSE NUMERIC(15, 4)
)
AS

BEGIN
   FOR
      SELECT
         L.STOCK_CODE, INV.DESCRIPTION, INV.STOCK_GROUP, L.BALANCE
      FROM
         LEDGER L LEFT JOIN INVENTORY INV ON L.STOCK_CODE = INV.STOCK_CODE
      WHERE
         (:MAIN_GROUP CONTAINING '~' || INV.STOCK_GROUP || '~')
--         INV.STOCK_GROUP in (:MAIN_GROUP)
        AND (L.LEDGER_ACCOUNT in ('71212', '71211' ,'83791'))
      INTO
         STOCK_CODE, STOCK_GROUP, DESCRIPTION, EXPENSE
   DO
      .....

Then you call like that:

SELECT * FROM SP_STOCK_ANALYSIS ('~1~4~8~11~')

You can convert a query returning ID's into string using LIST aggregating function, like

SELECT '~' || LIST (ID, '~') || '~' FROM source-table WHERE ........

But i think engineering wise it would be better to use transaction-local GTT (global temporary table) instead of double conversion, then do natural join inside your SP.

insert into SP-helping-GTT 
  SELECT ID FROM source-table WHERE ........;

...and then execute parameter-less SP, and then just COMMIT to cleanse the GTT

The drawbacks would be implicit strict coupling and namespace pollution, of course.

But since you do multiple filtering on TWO tables at once - L and INV - and you might want to convert both list into parameters, then joining two tables (GTTs) would be natural for relational database engine, and two nested natural scans would get poor O(n^2) scaling.

---

Additionally, you would really think if you need exotic CHAR datatype instead of VARCHAR. It truly is a minor nuissance, but people somehow get tripped over it again and again.

• Firebird queries using chars/varchar
• Trim Char fields
• Table name in Firebird has a lot of extra space
• Trim whitespace from right
• Getting the length of a string in SQL

...and those are only a few.