Is it possible to send a collection of ID's as a ADO.NET SQL parameter?

Question

Eg. can I write something like this code:

public void InactiveCustomers(IEnumerable<Guid> customerIDs)
{
    //...
    myAdoCommand.CommandText =
        "UPDATE Customer SET Active = 0 WHERE CustomerID in (@CustomerIDs)";
    myAdoCommand.Parameters["@CustomerIDs"].Value = customerIDs;
    //...
}

The only way I know is to Join my IEnumerable and then use string concatenation to build my SQL string.

Answer 1

Generally the way that you do this is to pass in a comma-separated list of values, and within your stored procedure, parse the list out and insert it into a temp table, which you can then use for joins. As of Sql Server 2005, this is standard practice for dealing with parameters that need to hold arrays.

Here's a good article on various ways to deal with this problem:

Passing a list/array to an SQL Server stored procedure

But for Sql Server 2008, we finally get to pass table variables into procedures, by first defining the table as a custom type.

There is a good description of this (and more 2008 features) in this article:

Introduction to New T-SQL Programmability Features in SQL Server 2008

Answer 2

You can with SQL 2008. It hasn't been out very long, but it is available.