Variable number of parameters in a Firebird stored procedure

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?

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