How to pass multiple values in a single parameter if number of values is not predefined

Question

I select some rows in dbgrid and then have to pass set of values in parameter of stored procedure or query. I use Firebird 3. How to pass multiple values in a single parameter if number of values is not predefined? For example, for 3 values of good_id I get error:

conversion error from string "7802 ,8403 ,11461"

create or alter procedure sp_goods (id varchar(60))
returns (
    good varchar(50),
    good_id integer)
as
begin
 for select good_id, good from goods where good_id in (:id)
 into :good_id, :good
 do suspend;
end


 procedure Button1Click(Sender: TObject);
    var
       str : String;  
       i  : Integer;      
     begin
     Query1.Close;
     Query1.SQL.Text := 'select * from sp_goods(:id) ';
    
     with DBGridGoods do
       begin
        if SelectedRows.Count > 0 then
          begin
             str := '';
             With DataSource.DataSet do
             for i := 0 to SelectedRows.Count - 1 do
             begin
                GotoBookmark(SelectedRows.Items[i]) ;
                str := str + FieldByName('good_id').asString + ', ';
             end;
          str := copy( str, 1, length( str ) - 2 );
          end;
       end;
    
     Query1.Params[0].AsString:=str;
     Query1.Open;

end;

If I call stored procedure in IBExpert

select * from sp_goods('8403') 

It works, but

select * from sp_goods('8403','7802')

returns error

Input parameter mismatch for procedure sp_goods.

The same error occurs if I use query instead of stored procedure.

I tried to use array for values, but get empty dataset:

 procedure Button1Click(Sender: TObject);
 var
   a: array of integer;
 begin
  Query1.Close;
  Query1.SQL.Text := 'select * from sp_goods(:id) ';
        
  setlength(a, 2);
  a[0]:= 7802;
  a[1]:=8403;
  Query1.Params[0].Value:= a;   
  Query1.Open;
end;     

Answer 1

There is no way to pass set of values into single parameter in Firebird.

In your example whole stored procedure is meaningless and it is simpler and faster to select all values at once into original grid using join. If you wish to get goods for selected items only and to put them into a separate grid the best way is to perform the query in your loop instead of gathering list of ids. If you prepare the query once (it is a common mistake to do prepare() call inside of the loop) it will be quite fast.

Answer 2

I have achieved this in two ways in the past. One is by using Dynamic Queries, which is not what you want to do, unless there is no other option.

The other way is by using this procedure. I am dragging this from my archives, and there will be other ways to achieve this more efficiently. I am providing it to show how to do it.

create or alter procedure "Split_Line"
(  IP_NOTE VARCHAR (16000),
   IP_SEP CHAR (1))
returns (
   "Index" INTEGER,
   "Line" VARCHAR (16000))
as
  declare variable lLines varchar (16000);
  declare variable lMax   integer;
  declare variable lPos   integer;
begin
  lMax   = 16000;
  lLines = ip_Note;
  "Index" = 0;
  while (lLines is not null)
  do begin
     "Line" = null;
     lPos   = null;
     select "Result" from "Pos" (:Ip_Sep, :lLines) into :lPos;
     if (lPos is null or lPos = 0)
     then begin
          /* Last line with no separator */
          "Line" = lLines;
          lLines = null;
     end
     else if (lPos = 1 and lLines = Ip_Sep)
     then begin
          /* Last char is a separator */
          "Line" = '';
          lLines = null;
     end
     else begin
          /* Normal Case */
//          "Line" = "SubStr" (:lLines, 1, :lPos-1);
//          lLines = "SubStr" (:lLines, :lPos+1, :lMax);
          "Line" = substring (:lLines from 1 for :lPos-1);
          lLines = substring (:lLines from :lPos+1 for :lMax);

     end
     "Index" = "Index" + 1;
     suspend;
  end
end

You call it with a comma separated values in a string and the separator character (comma in this case). And it returns a table that you use.

Example of usage

select * from "Split_Line" ('x,a,cat', ',')

will return

Index	Line
1	x
2	a
3	cat

And you can use it in your case

create or alter procedure sp_goods (id varchar(60))
returns (
    good varchar(50),
    good_id integer)
as
begin
 for select good_id, good from goods 
 where good_id in (select cast("Line" as numeric (18, 0)) 
                   from "Split_Line" (:id, ','))
 into :good_id, :good
 do suspend;
end

Supporting procedure to compile before Split_String

create or alter procedure "Pos"
(  SUBSTR VARCHAR (100),
   STR VARCHAR (16000))
returns (  "Result" INTEGER)
as
  DECLARE VARIABLE SubStr2 VARCHAR(16256); /* 1 + SubStr-lenght + Str-length */
  DECLARE VARIABLE Tmp VARCHAR(255);
BEGIN
  IF (SubStr IS NULL OR Str IS NULL)
  THEN BEGIN
       "Result" = NULL;
       suspend;
       EXIT;
  END
  IF (SubStr = '' OR Str = '')
  THEN BEGIN
       "Result" = 0;
       suspend;
       EXIT;
  END

  SubStr2 = SubStr || '%';
  Tmp = '';
  "Result" = 1;
  WHILE (Str NOT LIKE SubStr2 AND Str NOT LIKE Tmp)
  DO BEGIN
     SubStr2 = '_' || SubStr2;
     Tmp = Tmp || '_';
     "Result" = "Result" + 1;
  END

  IF (Str LIKE Tmp)
  THEN "Result" = 0;
  suspend;
END

And I have replaced my substr (from my user defined function library) to use the firebird substring in the Split_Line procedure.

Apologies for the quoted identifiers, always use Dialect 3. And the odd capitalisation was to support Crystal Reports which at that time would only work with uppercase procedures.

Answer 3

The other way to do it is to use Dynamic Queries.

create or alter procedure sp_goods (id varchar(60))
returns (
    good varchar(50),
    good_id integer)
as
 declare lsql varchar (5000);
begin
 lsql = 'select good_id, good from goods where good_id in (' || :id || ')';
 for execute statement lsql  
 into :good_id, :good
 do suspend;
end

Disadvantages

1. Normally, when a procedure is compiled, the queries are prepared at that time. So, execution is faster. With Dynamic Queries or Dynamic Sql, the query has to be prepared every time the procedure is executed.
2. Normally, when a procedure is compiled, the engine validates the table and fields etc. In this case the validation happens at execution time. So you have to be really careful how you construct your query.

Note - I havent had time to test it with a real table, but it compiles. (Its 3am, so I might check that tomorrow). I wouldn't normally recommend this, but everything has a place.