Use of parameters in the "in" clause in a Tibquery

Question

I have the following SQL command in the SQL property of a Tibquery component in Delphi.

SQL command used in the component with parameters:

select pro_codigo, pro_nome 
  from produtos 
 where pro_fis_codigo = :fis_codigo 
   and pro_bloqueado in (:bloqueado)

SQL command without the parameters:

select pro_codigo, pro_nome 
  from produtos 
 where pro_fis_codigo = 1 
   and pro_bloqueado in (1,2)

Is there any way to use the parameter :bloqueado as an example below?

Query.PramByName(bloqueado).AsString:= '1,2'

Answer 1

There is no way to do what you want using parameters. Parameters don’t work that way. You can try some alternatives, as:

• Change the SQL "in hand" as Filipe Fonseca replies
• Use temporary tables and make a Join
• Use a process that transforms the parameter into a list
• other darker ways :)

Answer 2

Since you’re interested in an outline, follow:

Instead of passing a parameter only with everything you want to put, try the following.

IBQuery1.SQL.Clear;
IBQuery1.SQL.Text := Format('Select pro_codigo, pro_nome from produtos where pro_fis_codigo = :fis_codigo and pro_bloqueado in (%s)',[QuotedStr('ParametroVarChar') + ',' + 'Parametro Numero']);

So, inside the brackets you will set your integer parameter.

Answer 3

query := '(1,28,32,88,91,9355)';

IBQProduto.Close;
IBQProduto.SQL.Clear;
IBQProduto.SQL.Add('select * from produto pro');
IBQProduto.SQL.Add('where pro.item in ' + query);
IBQProduto.SQL.Add('order by pro.item asc');
IBQProduto.Open;