How to input an array parameter of values to Firebird Stored Procedure?

Question

I would like to input an array parameter of IDs to Firebird Stored Procedure.

:INPUT_LIST_ID = [1, 2, 12, 45, 75, 45]

I'm need to execute this SQL command:

SELECT *
FROM CITY
WHERE ID_CITY IN (:INPUT_LIST_ID)

Is it possible? Thanks!

Answer 1

If you use Firebird 1.5 (It should work on higher versions too), you can use this simple function i made to convert a single string into an integer array:

create or alter procedure INTEGER_LIST (
    input varchar(4096))
returns (
    INT_VALUE integer)
as
declare variable CHAR_COUNT integer;
declare variable PARAM_LENGTH integer;
declare variable READ_VALUE char(1);
declare variable CURRENT_INTEGER varchar(20);
begin
    param_length = strlen(input);
    char_count = 0;
    current_integer = '';
    while (char_count < param_length) do begin
        char_count = :char_count + 1;
        read_value = substr(:input, :char_count, :char_count);
        if (:read_value <> ',') then begin
            current_integer = :current_integer || :read_value;
        end else if (:read_value <> ' ') then  begin
            int_value = cast(:current_integer as integer);
            current_integer = '';
            suspend;
        end

        if (:char_count = :param_length) then begin
            int_value = cast(:current_integer as integer);
            suspend;
        end
    end
end

Usage

select int_value from integer_list('1,2,3,4, 5, 200, 1, 598415, 2')

Will return this:

INT_VALUE
1
2
3
4
5
200
1
598415
2

Answer 2

AFAIK no, thats not possible. While Firebird does have array data type, support for it is rudimentary and use of arrays is generally not recommended. I think the easiest solution would be to pass the array as (comma separated) string and then use the for execute statement statement to get the resultset, something like

create procedure CITY (INPUT_LIST_ID varchar(1024)) 
returns( ... )
as
begin
  for execute statement
    'select ... from T where ID_CITY IN ('|| INPUT_LIST_ID ||')' into ...
  do begin
     suspend;
  end
end

This means however that the statement you use to get the result also changes, instead of WHERE you would use the parameter of the stored procedure CITY:

SELECT * FROM CITY('1, 2, 12, 45, 75, 45')

Another option to send the parameter list is to use global temporary table. This has the pro that you can send huge number of IDs without exceeding the maximum allowed statements size but it is more work to set up the call...

create global temporary table SP_CITY_PARAMS (
  id int not null primary key
)
on commit delete rows;

create procedure CITY
returns( ... )
as
begin
  for select ... from T where ID_CITY IN (
      select id from SP_CITY_PARAMS
  ) into ...
  do begin
     suspend;
  end
end

Answer 3

You could also use something like this:

SELECT *
FROM CITY
WHERE ID_CITY IN (SELECT ID FROM GetIntegerList('1, 2, 12, 45, 75, 45'))

You would have to create a new Firebird Procedure called "GetIntegerList" which would look something like this:

CREATE OR ALTER PROCEDURE "GETINTEGERLIST"("AINTEGERLIST" VARCHAR(32000))
returns (
  ID integer
)
as
  declare variable IntegerList varchar(32000);
  declare variable CommaPos integer;
  declare variable IntegerVal varchar(10);
begin
  IntegerList = AIntegerList || ' ';
  CommaPos = Position(',', IntegerList);

  while (CommaPos > 0) do
  begin
    IntegerVal = Trim(SubString(IntegerList from 1 for CommaPos - 1));

    if (Char_Length(IntegerVal) > 0) then
    begin
      if (IntegerVal similar to '[0-9]*') then
      begin
        ID = Cast(IntegerVal as integer);
        suspend;
      end
    end

    if (Char_Length(IntegerList) > CommaPos) then
      IntegerList = SubString(IntegerList from CommaPos + 1);
    else
      IntegerList = '';

    CommaPos = Position(',', IntegerList);
  end

  IntegerList = Trim(IntegerList);

  if (Char_Length(IntegerList) > 0) then
  begin
    if (IntegerList similar to '[0-9]*') then
    begin
      ID = Cast(IntegerList as integer);
      suspend;
    end
  end
end

Note, this was done in Firebird 2.5.2.

Answer 4

Try this:

    SELECT *
    FROM CITY
    WHERE '/city1/city2/city.../' containing '/' || ID_CITY || '/';