How to pass an array into a SQL Server stored procedure

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

SQL Server 2016 (or newer)

You can pass in a delimited list or JSON and use STRING_SPLIT() or OPENJSON().

STRING_SPLIT():

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List varchar(max)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT value FROM STRING_SPLIT(@List, ',');
END
GO
EXEC dbo.DoSomethingWithEmployees @List = '1,2,3';

OPENJSON():

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List varchar(max)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT value FROM OPENJSON(CONCAT('["',
    REPLACE(STRING_ESCAPE(@List, 'JSON'), 
    ',', '","'), '"]')) AS j;
END
GO
EXEC dbo.DoSomethingWithEmployees @List = '1,2,3';

I wrote more about this here:

  • Handling an unknown number of parameters in SQL Server
  • Ordered String Splitting in SQL Server with OPENJSON

SQL Server 2008 (or newer)

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

CREATE TYPE dbo.IDList
AS TABLE
(
  ID INT
);
GO

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List AS dbo.IDList READONLY
AS
BEGIN
  SET NOCOUNT ON;
  
  SELECT ID FROM @List; 
END
GO

Now in your C# code:

// Obtain your list of ids to send, this is just an example call to a helper utility function
int[] employeeIds = GetEmployeeIds();

DataTable tvp = new DataTable();
tvp.Columns.Add(new DataColumn("ID", typeof(int)));

// populate DataTable from your List here
foreach(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 TABLE
AS
  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)
AS
BEGIN
  SET NOCOUNT ON;
  
  SELECT EmployeeID = Item FROM dbo.SplitInts(@List, ','); 
END
GO

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)
AS
BEGIN
  SELECT EmployeeID 
  FROM EmployeesTable
  -- inner join AnotherTable on ...
  where @List like '%;'+cast(employeeID as varchar(20))+';%'
END
GO

Tags:

C#

Sql Server

Tsql

Stored Procedures

Recent Posts