Delphi: how to pass a list as a parameter to a SQL query?
delphi, delphi-7, sql, sql-server
Solution
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']);
Problem
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.