How do you send lists of values in a SQL Server select statement programmatically in a nice way?

Question

Hi,

My program generates a list at runtime of values. I need to send a sql query that looks in a table for entries that have a column that contain one of the values in the list. I can't use the usual chain of OR's because I don't know how big the list will be. Is there a nice way to use an array or some IEnumerable to build a SQL statement that makes a big chain of OR's for me? Using C# BTW

I'm using SQL Server but I'd prefer something that works across all databases if such a thing exists.

Thanks!

Answer 1

Try this:

SELECT * FROM table WHERE column IN (@param)

And make sure you turn @param into the form "'@p1, @p2, @p3'" by using your chosen language's implode function.

The query should be standard for all SQLs I know.

Alternatively if you want to do a LIKE comparison:

SELECT * FROM table WHERE @param LIKE '%' (COALLESCE) column (COALLESCE) '%'

@param is still a quoted string, but you need to use a different COALLESCE operator depending on your database (SQL Server: '+', MySQL: '.', Oracle: '|').

Example PHP implode code:

$ps = array('1', 'test', '323');
$param = implode(',', $ps);

Example C# "implode" code:

string[] ps = new string[] { "test", "blah", "boo" };
string param = string.Join(",", ps));

Answer 2

Read up Erland Sommarskog's excellent Arrays and Lists in SQL Server article - he explains in great detail and with great insights what can be done in which version of SQL Server.

I also believe this is going to be database-specific, I don't see any approaches that would be ANSI SQL compliant or work on other databases, too - string and XML handling is just too specific for each database, I guess.

Answer 3

One thing you can try is table-valued parameters in stored procedures in MS-SQL:

http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters

Not only is that unmessy since all you're going to do is add rows to your table-valued parameter for each value in your list, but it also executes quickly compared to the alternatives since the SQL DBMS can reuse its execution plan every time you run the query given that the number of arguments never changes.