How to format a string as a parameter for IN clause

[kk]

I just converted an app from BDE to SDAC but the query does not returns data anymore. I found out that the problem is with the string that i pass in the IN clause. The query is like this.

SELECT *
FROM tblABC
WHERE Item IN(:Item)

:Item = 'Item1','Item2'
This fails.

The query works if i set parameter to only one Value

:Item = 'Item1'
This works.

How should i format the string?
It used to work in BDE though.

[Ikar]

Looks like MS SQL Server restriction. Try to use macros instead of parameters:

Code: Select all

   MSQuery.SQL.Text := 'SELECT au_lname, state FROM pubs..authors WHERE state IN (&p)';
   MSQuery.Macros[0].Value := '''CA'', ''IN'', ''MD''';
   MSQuery.Execute;

PS: curiously, that BDE processes these queries at you.
If you send us BDE-example we'll try to reproduce this behaviour.

[Guest]

Thanks for your reply.
Your components are excellent. Well done and keep it up

PS: curiously, that BDE processes these queries at you.
If you send us BDE-example we'll try to reproduce this behaviour.

Im sorry im mistaken, BDE has the same problem. I was generating the SQL command in code and passing to the Query component so the query worked in BDE.