Passing an "in" list via stored procedure [duplicate]

Question

How can I construct a stored procedure that will allow me to pass (for example) an @IDList so that I can write:

Select * from Foo Where ID in @IDList

Is this doable?

Answer 1

With SQL2005 and above you can send an array from code directly.

First create a custom type

CREATE TYPE Array AS table (Item varchar(MAX))

Than the stored procedure.

CREATE PROCEDURE sp_TakeArray
    @array AS Array READONLY
AS BEGIN
    Select * from Foo Where ID in (SELECT Item FROM @array)
END

Then call from code passing in a DataTable as the array

DataTable items = new DataTable();
items.Columns.Add( "Item", typeof( string ) );

DataRow row = items.NewRow();
row.SetField<string>( "Item", <item to add> );
items.Rows.Add( row );

SqlCommand command = new SqlCommand( "sp_TakeArray", connection );
command.CommandType = CommandType.StoredProcedure;
SqlParameter param = command.Parameters.Add( "@Array", SqlDbType.Structured );
param.Value = items;
param.TypeName = "dbo.Array";

SqlDataReader reader = command.ExecuteReader();