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

Question

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

Navigation

#1 by (2 votes)
#2 by (2 votes)
#3 by (0 votes)

4

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

SQL command used in the parameterized component:

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

SQL command without 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 :bloqueado parameter as shown below?

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

delphi

asked by anonymous 11.06.2014 / 14:17

3 answers

When to use each data type of mysqli_stmt_bind_param Program to filter odd numbers in a list of numbers

score 2 · Answer 1

As you are interested in an outline, it follows:

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, within the square brackets you will define your integer parameter.

score 2 · Answer 2

There is no way to do what you want by using parameters. Parameters do not work like that . You can try some alternatives , such as:

Change SQL "on hand" as answered by Filipe Fonseca
Use temporary tables and make a join
Use a procedure that turns the parameter into a list
other more obscure ways:)

score 0 · 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;