How to get a set of rows from an array of id's ?

Question

In C#, I have an array of integers, that represent the primary keys of rows in a Table in SQL Server. I need to select all these rows from SQL Server by executing a single Select command - preferably through a stored procedure.

There may be from a few to hundreds of ID's in the array, and the solution needs to work on SQL Server 2005 and 2008.

What is the best/most efficient way to do this ?

I currently have a solution, where I pass a string containing a comma-separated list of the ids to SQL Server, and then generating a SELECT statement based on this. I am hoping there is a better way, that do not involve dynamically generating the SQL statements.

Answer 1

Linq2SQL supports this by doing a where idList.Contains(record.id); In ADO.Net there is no real solution with TableAdapters, but this article should get it working:

http://support.microsoft.com/kb/555266/en-us

Answer 2

You can create a stored procedure which takes a TABLE data type as an input parameter and joins this parameter with your data table. In your C# code, you create a DataTable containing the IDs and pass this DataTable to your stored procedure as a parameter.

Here's a detailed description on how to do this: http://msdn.microsoft.com/en-us/library/bb675163.aspx

EDIT: I'm not sure if this works with SQL Server 2005 or only with 2008...

Answer 3

I've had a lot of success using OPENXML with SQL Server. Pass in an unlimited array of values using either TEXT or XML data types, then use that parameter as a join or sub-select to return the data you want.

I've used this since SQL Server 2000 was released and it runs like a champ.

Answer 4

I'm with you. Using a comma delimited string is not a bad approach. But you can create a Table-Valued Function to generate a table on the fly and join it with your target table.

Create Function [dbo].[Split]
(
    @Array   nvarchar(4000),
    @Separator  char,
    @ToLower  bit = 0
)
Returns 
@Result Table 
(
    ItemKey  int Identity(1, 1) Not Null,
    ItemValue  nvarchar(256) NULL
)
AS
BEGIN

    Declare @Index int,
      @Value nvarchar(256)

    Select @Index = 0
    Select @Value = Null

    While (1 = 1)
    Begin

     Select @Index = CharIndex(@Separator, @Array)

     If (@Index = 0)
     Begin
      Insert Into @Result Values (LTRIM(RTRIM(Case @ToLower When 1 Then Lower(@Array) Else @Array End)))
      Break
     End

     Select @Value = SubString(@Array, 0, @Index)

     Insert Into @Result Values (LTRIM(RTRIM(Case @ToLower When 1 Then Lower(@Value) Else @Value End)))

     Select @Array = Right(@Array, Len(@Array) - @Index)

    End

    Return

END

---

Select *
from dbo.TargetTable tt, dbo.Split('101, 102, 103', ',', 0) r
Where (tt.PrimaryKey = r.ItemValue)

Answer 5

One option is to pass the list as an xml document. Such a document is easy to create with .NET serialization:

var yourList = new List<int>() { 1, 2, 3 };
using (var stream = new MemoryStream())
using (var writer = XmlWriter.Create(stream))
{
    new XmlSerializer(yourList.GetType()).Serialize(writer, yourList);
    var xmlEncodedList = Encoding.UTF8.GetString(stream.ToArray());
}

You can parse the document in SQL Server like:

declare @list xml
set @list = '<?xml version="1.0" encoding="utf-8"?><ArrayOfInt 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;
<int>1</int><int>2</int><int>3</int></ArrayOfInt>'

select list.a.value('.','int')
from @list.nodes('/ArrayOfInt/int') as list(a)

Now that you've changed the XML into a query, you can do anything with it. Store it in a temporary table, or join it on another table. Here's an example procedure that retrieves specific rows from a table:

create procedure testproc(@list as xml)
as
select *
from TheTable
where ID in (
    select list.a.value('.','int')
    from @list.nodes('/ArrayOfInt/int') as list(a)
)