How to pass an array into a SQL Server stored procedure

SQL Server 2008 (or newer)

First, in your database, create the following two objects:

CREATE TYPE dbo.IDListAS TABLE(  ID INT);GOCREATE PROCEDURE dbo.DoSomethingWithEmployees  @List AS dbo.IDList READONLYASBEGIN  SET NOCOUNT ON;  SELECT ID FROM @List; ENDGO

Now in your C# code:

// Obtain your list of ids to send, this is just an example call to a helper utility functionint[] employeeIds = GetEmployeeIds();DataTable tvp = new DataTable();tvp.Columns.Add(new DataColumn("ID", typeof(int)));// populate DataTable from your List hereforeach(var id in employeeIds)    tvp.Rows.Add(id);using (conn){    SqlCommand cmd = new SqlCommand("dbo.DoSomethingWithEmployees", conn);    cmd.CommandType = CommandType.StoredProcedure;    SqlParameter tvparam = cmd.Parameters.AddWithValue("@List", tvp);    // these next lines are important to map the C# DataTable object to the correct SQL User Defined Type    tvparam.SqlDbType = SqlDbType.Structured;    tvparam.TypeName = "dbo.IDList";    // execute query, consume results, etc. here}

SQL Server 2005

If you are using SQL Server 2005, I would still recommend a split function over XML. First, create a function:

CREATE FUNCTION dbo.SplitInts(   @List      VARCHAR(MAX),   @Delimiter VARCHAR(255))RETURNS TABLEAS  RETURN ( SELECT Item = CONVERT(INT, Item) FROM      ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')        FROM ( SELECT [XML] = CONVERT(XML, '<i>'        + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')          ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y      WHERE Item IS NOT NULL  );GO

Now your stored procedure can just be:

CREATE PROCEDURE dbo.DoSomethingWithEmployees  @List VARCHAR(MAX)ASBEGIN  SET NOCOUNT ON;  SELECT EmployeeID = Item FROM dbo.SplitInts(@List, ','); ENDGO

And in your C# code you just have to pass the list as '1,2,3,12'...

I find the method of passing through table valued parameters simplifies the maintainability of a solution that uses it and often has increased performance compared to other implementations including XML and string splitting.

The inputs are clearly defined (no one has to guess if the delimiter is a comma or a semi-colon) and we do not have dependencies on other processing functions that are not obvious without inspecting the code for the stored procedure.

Compared to solutions involving user defined XML schema instead of UDTs, this involves a similar number of steps but in my experience is far simpler code to manage, maintain and read.

In many solutions you may only need one or a few of these UDTs (User defined Types) that you re-use for many stored procedures. As with this example, the common requirement is to pass through a list of ID pointers, the function name describes what context those Ids should represent, the type name should be generic.

Based on my experience, by creating a delimited expression from the employeeIDs, there is a tricky and nice solution for this problem. You should only create an string expression like ';123;434;365;' in-which 123, 434 and 365 are some employeeIDs. By calling the below procedure and passing this expression to it, you can fetch your desired records. Easily you can join the "another table" into this query. This solution is suitable in all versions of SQL server. Also, in comparison with using table variable or temp table, it is very faster and optimized solution.

CREATE PROCEDURE dbo.DoSomethingOnSomeEmployees  @List AS varchar(max)ASBEGIN  SELECT EmployeeID   FROM EmployeesTable  -- inner join AnotherTable on ...  where @List like '%;'+cast(employeeID as varchar(20))+';%'ENDGO

Use a table-valued parameter for your stored procedure.

When you pass it in from C# you'll add the parameter with the data type of SqlDb.Structured.

See here: http://msdn.microsoft.com/en-us/library/bb675163.aspx

Example:

// Assumes connection is an open SqlConnection object.using (connection){// Create a DataTable with the modified rows.DataTable addedCategories =  CategoriesDataTable.GetChanges(DataRowState.Added);// Configure the SqlCommand and SqlParameter.SqlCommand insertCommand = new SqlCommand(    "usp_InsertCategories", connection);insertCommand.CommandType = CommandType.StoredProcedure;SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(    "@tvpNewCategories", addedCategories);tvpParam.SqlDbType = SqlDbType.Structured;// Execute the command.insertCommand.ExecuteNonQuery();}