Passing an "in" list via stored procedure...

Duplicate of: http://stackoverflow.com/questions/43249/t-sql-stored-procedure-that-accepts-multiple-id-values/43767#43767

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?

TIA! Kevin

From stackoverflow

• Write the individual IDs to table B, all with the same "key" (a GUID perhaps).
  Then, your query against table A would include

  where ID in (select ID from B where key = @TempKey)
  

  (You might then delete the keys if you are finished with them. Or, timestamp them and have a sql job do it later.)

  Pros:

  • You don't send a string, which could expose you to sql injection in some circumstances.
  • Depending on your other app logic, you don't have to track or write the possibilities all at once.

  Cons:

  • It could be extremely inefficient, especially under heavy loads.

• see this answer...

  http://stackoverflow.com/questions/43249/t-sql-stored-procedure-that-accepts-multiple-id-values/43767#43767

• 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();