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