Delphi: How to Pass a List as a Parameter to a SQL Query

Delphi: how to pass a list as a parameter to a SQL query?

AFAIK, it is not possible directly.

You'll have to convert the list into a SQL list in plain text.

For instance:

function ListToText(const Args: array of string): string; overload;
var i: integer;
begin
  result := '(';
  for i := 0 to high(Args) do 
    result := result+QuotedStr(Args[i])+',';
  result[length(result)] := ')';
end;

function ListToText(const Args: array of integer): string; overload;
var i: integer;
begin
  result := '(';
  for i := 0 to high(Args) do 
    result := result+IntToStr(Args[i])+',';
  result[length(result)] := ')';
end;

To be used as such:

SQL.Text := 'select * from myTable where intKey in '+ListToText([1,2,3]);
SQL.Text := 'select * from myTable where stringKey in '+ListToText(['a','b','c']);

How can I pass a list of values as a parameter for a TOraQuery?

You can use a "Macro"

Not quite what I was looking for, but it's a hair closer to a parameter than building the SQL on the fly.

Create the TOraQuery like this

SELECT ML.ID, Ml.detail1, Ml.detail2
FROM MY_LIST ML 
WHERE 
    ML.ID in (&My_IDS)

Now I can pass in 14001,14002,14003 as a macro.

myListQuery.Active := False;
myListQuery.MacroByName('My_IDS').value := '14001,14002,14003';
myListQuery.Active := True;

Array parameter for TADOQuery in Delphi 2010

There is no parameter type that can be used to pass a list of values to in. Unfortunately, this is one of the shortcomings of parameterized SQL.

You'll have to build the query from code to either generate the list of values, or generate a list of parameters which can then be filled from code. That's because you can pass each value as a different parameter, like this:

SELECT * FROM MyTable WHERE Id IN (:id1, :id2, :id3)

But since the list will probably have a variable size, you'll have to alter the SQL to add parameters. In that case it is just as easy to generate the list of values, although parametereized queries may be cached better, depending on which DB you use.

SQL Query Parameters

Parameters automatically put the quotes around strings for you. Therefore, you cannot include such quotes in the SQL query. Because of that, you also cannot pass the % syntax with the query either.

Instead, keep your statement as the original (replacing = with LIKE), and pass the % around the actual value instead.

Query = 'SELECT * from table_1 where code LIKE :value';
Param := ADOQuery1.Parameters.ParamByName('value');
Param.DataType := ftString;
Param.Value := '%bob%';

---

On a side note, this code's a bit more elegant...

ADOQuery1.Parameters.ParamValues['value']:= '%bob%';

No need to tell it it's a string. It will detect that automagically.

How to use parameters in TpFIBDataSet SQL query?

I don't have a copy of the TpFIBDataSet handy. but usually you set a dataset parameter by code like this:

MyDataSet.Parameters.ParamByName('BROJ').AsString := 'some value';

Note : With some TDataSet descendants, the parameters collection is named Params rather than Parameters.

I gather from your suggested edit that with the dataset type you are using, you can write this instead:

MyDataSet.ParamByName('BROJ').AsString := 'some value';

Pass parameter by in Delphi

try this :

p_in  : String ;

for i := 0 to Customerlist.count do
   p_in := p_in +Customerlist[i] + ','; 

MyQuery.text := 'select * from customers where id in (' + p_in  + ' )' ;

How to pass DataSet: TDataSet as procedure parameter

If it is attached to the event, it is the dataset that triggered the AfterOpen event. The dataset itself will call the procedure, and pass itself in that parameter.

But you added the Info parameter, which makes the procedure invalid as an event handler. Where do you want that info to come from? From the dataset?

Since it's an event handler, it's bad practise to call it yourself. You could do it, and just pass nil (or a specific dataset), since it's not used anyway. But you can get into weird situations, because it looks like the method is only going to be called after open, but then it turns out it's called on other occasions as well.
So it's better to make a separate procedure to do what you want, and call that from the AfterOpen event handler. You can pass in info from the dataset, but you can also call that procedure from somewhere else, for instance to provide some initial caption until the dataset is opened:

// The procedure doesn't need the dataset, only the info to set.
procedure Tf_SeznamDluzniku.ShowInfoMessage(Info : string);
begin
  l_InfoMessage.Caption := info;
end;

// The actual event handler for YourDataset.OnAfterOpen (you have to attach them)
// This can set the info, and/or maybe set the success indicator right away..
procedure Tf_SeznamDluzniku.YourDataSetAfterOpen(DataSet: TDataSet);
begin
  ShowInfoMessage(DataSet.FieldByName('info').AsString);
  img_success.Visible := True;
end;

// For demonstration, another event handler for showing the form, to put in some initial caption.
procedure Tf_SeznamDluzniku.FormShow(const Sender: TObject);
begin
  ShowInfoMessage('Loading...');
end;

Delphi query multi Parameters

You should check if textboxes are empty;

begin
    adoquery1.close;
    adoquery1.sql.clear;
    adoquery1.sql.add(SELECT * FROM Table WHERE 1=1 );
    if edtName.text <> '' then
      Begin
        adoquery1.sql.add(' And name=:name ');
        adoquery1.Parameters.ParamByName('name').Value:= edtName.text;
      End;
    if edtTel.text <> '' then
      Begin
        adoquery1.sql.add(' And tel=:tel ');
        adoquery1.Parameters.ParamByName('tel').Value:= edtTel.text;
      End;
    adoquery1.open;
end;

note: if both of them are empty result will show all records.

---

Related Topics