Pass an array as value in an ado.net DBParameter

Question

The project I'm working on has a lot of IN-queries like:

SELECT something, anotherthing
FROM atable
WHERE something IN (value1, value2, value3)

This is an example of a query with 3 parameters in the IN-part but the same query could be executed with 1 or 2 or 5 or 10 or ... parameters. The problem is that each query has an other execution plan in the database with makes it slow.

I'd like to hava a query like this:

SELECT something, anotherthing
FROM atable
WHERE something IN (@value1, @value2, @value3)

or this:

SELECT something, anotherthing
FROM atable
WHERE something IN (@values)

I have accomplished the first query with some helper function, but I still have different execution plan per number of parameters. This could be solved with the second one.

What is the best way to pass an array as database parameter? I'm using Oracle and SQL Server, solutions for both of them are welcome.

Answer 1

For SQL-Server, there are two common approaches for this. The third option to be avoided is to pass in a varchar and concatenate it into a dynamic SQL statement with IN - this is a clear injection attack surface.

Reasonable options:

• pass in a varchar and use a UDF to split the data on a delimiter (like in this question), perhaps comma, pipe, tab, etc. Join to the result:

  SELECT something, anotherthing
  FROM atable a
  INNER JOIN dbo.SplitUDF(@values) udf
          ON udf.Value = a.something
  

• use a table-valued-parameter (SQL2008) and join directly (avoid the UDF)

Answer 2

Have a look at these articles

• MSSQL Limitations and Arrays
• Passing Arrays in SQL Parameters using XML Data Type in SQL Server 2005
• Since there is no Sqlserver array parameter, what’s the best way to proceed?
• Arrays and Lists in SQL Server 2005

This is an example of using the XML type to create a list

--Split
DECLARE @textXML XML
DECLARE @data NVARCHAR(MAX), 
        @delimiter NVARCHAR(5)

SELECT  @data = 'A,B,C',
        @delimiter = ','

SELECT    @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML)
SELECT  T.split.value('.', 'nvarchar(max)') AS data
FROM    @textXML.nodes('/d') T(split)