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

Question

I have a list of integers or of strings and need to pass it as a parameter for a Delphi DataSet. How to do it?

Here is an example. MyQuery is something like:

select * from myTable where intKey in :listParam

I'd set a parameter as a list or array or something else:

MyQuery.ParamByName('listParam').AsSomething := [1,2,3];

and it would result in this query sent to the sql server:

select * from myTable where intKey in (1, 2, 3)

It would be even better if the solution would also work with strings, making this query:

select * from myTable where stringKey in :listParam

become:

select * from myTable where stringKey in ('a', 'b', 'c')

I believe this is a simple question, but "IN" isn't a good keyword for searching the web.

Please answer how I should configure the parameter in the IDE, the query and how to pass the parameters.

I'm using Delphi 7.

Edited: I'm considering the answer is "it isn't possible to do directly". If someone give me a non-hackish answer, the accepted answer will be changed.

Answer 1

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']);