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

Answer 2

SQL accepts only single values as parameters so you cannot create a statement with one parameter that can map to a variable number of values, such as the example you gave.

However, you can still use parameterized SQL in this situation. The solution is to iterate over the list of values you have, adding a parameter marker to the SQL and a parameter to the parameter list for each value.

This is easiest to do with positional rather than named parameters but can be adapted for named parameters as well (you may need to adjust this code since I don't have Delphi available and don't remember the Parameter creation syntax):

 //AValues is an array of variant values
 //SQLCommand is some TDataSet component with Parameters.
 for I := Low(AValues) to High(AValues) do
 begin

    if ParamString = '' then
       ParamString = '?'
    else
      ParamString = ParamString + ', ?';

    SQLCommand.Parameters.Add(AValues[I]);

  end

  SQLCommand.CommandText = 
     'SELECT * FROM MyTable WHERE KeyValue IN (' + ParamString + ')';

This will produce an injection-safe parameterized query.

Answer 3

There are several options for you but basically you need to get your values into a table. I would suggest a table variable for that.

Here is a version that unpacks an int list.

declare @IDs varchar(max)
set @IDs = :listParam

set @IDs = @IDs+','

declare @T table(ID int primary key)

while len(@IDs) > 1
begin
  insert into @T(ID) values (left(@IDs, charindex(',', @IDs)-1))
  set @IDs = stuff(@IDs, 1, charindex(',', @IDs), '')
end

select *
from myTable
where intKey in (select ID from @T)

It is possible to have multi-statement queries. The parameter :listParam should be a string:

MyQuery.ParamByName('listParam').AsString := '1,2,3';

You can use the same technique for strings. You just need to change the data type of ID to for instance varchar(10).

Instead of unpacking with a while loop you could make use of a split function

declare @T table(ID varchar(10))

insert into @T 
select s
from dbo.Split(',', :listParam)

select *
from myTable
where  charKey in (select ID from @T)

A string param could look like this:

MyQuery.ParamByName('listParam').AsString := 'Adam,Bertil,Caesar';

Answer 4

Create a temporary table and insert your values in it. Then use that table as part of a subquery.

For example, create MyListTable in your database. Insert your values into MyListTable. Then do

select * from myTable where keyvalue in (select keyvalue from MyListTable)

This avoids SQL injection attacks. But it's not elegant, is not performance friendly because you have to insert records before running your query, and can lead to concurrency issues.

Not my first choice to deal with your situation but it addresses your concern about sql injection.

Answer 5

If someone still having the same problem, if you are using firedac you can use macros like this:

Query -> "select * from myTable where intKey in (&listParam)"

Setting the macro -> MyQuery.MacroByName('listParam').AsRaw := '1, 2, 3';

Answer 6

I use some "IN" replacement. Here is the query I use:

SELECT * FROM MyTable WHERE CHARINDEX(','+cast(intKey as varchar(10))+',', :listParam) > 0

the code to send parameter:

MyQuery.ParamByName('listParam').AsString := ',1,2,3,';  

The array item value can partially match some other values. For instance, "1" can be part of "100". To protect against it, I use comma as delimiter

Answer 7

Why not make a dynamic sql:

Quick and dirty, but still using parameters. check 10 elements. I don't know how well this scales.

    MyQuerySQL.Text:='SELECT * FROM myTable WHERE intKey in (:listParam0'
    for i := 1 to 9 do begin
      MyQuerySQL.Text := MyQuerySQL.Text + ',:listParam'+IntToStr(i)
    end;
    MyQuerySQL.Text := MyQuerySQL.Text+')';
    for i:=0 to 9 do begin
      MyQuery.ParamByName('listParam'+IntToStr(i)).AsInteger := ArrayofInt[0];
    end;