Pass an array into a SQL Server stored procedure?

In this article we will see how to pass an array into a SQL Server stored procedure. For example, we have a list of employees. we want to use this list as a table and join it with another table. But the list of employees should be passed as parameter from C#.

we can use the User Defined Data Type (UDT) with a base type of table to send array or list through a parameter. Let’s see with an example how to use User Defined Type to pass a list or an array to a stored procedure.

In this example, I will be passing a list of Employee IDs and Employee Names to the stored procedure. 

CREATE TYPE dbo.IDList AS TABLE
    ( 
        EmployeeID INT,
    )
GO

Create a stored procedure with the above created UDT as a parameter.

1 2 3 4 5 6 7 8 9

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

Now in your C# code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

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.GetEmployees", 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 }

For more understanding check this video: