By default stored procedures only allow you to use scalar values as parameters. Sometimes you would like to use a list of values, for instance when selecting a set of items or inserting multiple values in one statement.

Before I would either use a comma separated string that would be parsed or an XML type. Recently I discovered a third – probably better – way of doing this: by using a User Defined Table Type. This is supported from SQL Server 2008.

First you create the User Defined Table Type:

CREATE TYPE VarcharList AS TABLE (
	Value varchar(50) NOT NULL PRIMARY KEY
)

Then you use it as a parameter in a stored procedure:

CREATE PROCEDURE GetItemsByIDs
    @ids VarcharList READONLY
AS
BEGIN
    SELECT * FROM Items
    WHERE ID IN (SELECT Value FROM @ids);
END

The parameter has to be defined as readonly to make this work.

In your code calling the stored procedure you have to add a parameter using the SqlDbType.Structured type. You set the TypeName property with the name of the Table Type you just created.

SqlParameter sqlParameter = command.Parameters.Add("ids",
    SqlDbType.Structured);
sqlParameter.TypeName = "VarcharList";
var sqlMetaData = new SqlMetaData("Value", 
    SqlDbType.VarChar, 50);
sqlParameter.Value = IDs.Select(id =>
{
    var record = new SqlDataRecord(sqlMetaData);
    record.SetString(0, id);
    return record;
});

One of the possible types you can use as the value for the parameter is IEnumerable. In the example above Linq is used to create it.

Interestingly you will get an error when the parameter value doesn’t contain any rows. I added an extra argument check to prevent this:

public IList<Item> GetItemsByIds(ICollection<string> ids)
{
    if (ids == null) throw new ArgumentNullException("ids");
    if (ids.Count == 0) throw new ArgumentException
        ("Collection cannot be empty", "ids");

I have not any performance tests, but I imagine this will perform better than the other options because the parameters are already structured like the SQL server native types.