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

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') gets error

Input parameter mismatch for procedure sp_goods.

The same error occurs if I use query instead of sp.

UPDATE: I tried 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;

CodePudding user response：

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.

CodePudding user response：

What I have done in the past is to pass a comma separated string as an argument. And use that in the procedure with in (list) clause, assuming its of the format (123,456,78).

procedure name (ip_list varchar (255))
returns field list
as
//declare local variables
begin
select fields from table where field in :ip_list
into local variables;
//assign values to field list
suspend;
end