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

Question

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

Answer 1

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.

Answer 2

see this answer...

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

Answer 3

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