How to pass an array into a SQL Server stored procedure

Resource from StackOverflow

How do I pass array parameters using stored procedures?

1. It's too troublesome to split and parse strings.
2. Add Sql Server custom type sp_addtype

Problem requirements: Array type parameters need to be passed to SP
select * from Users where ID IN (1,2,3 )

Sql Server data types do not have arrays, but allow custom types through sp_addtype
Adding a custom data type allows c code to pass an array type parameter to sp
But instead of using sp_addtype directly, you need the data format of the structure type, as follows:

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

It's kind of like a temporary table, an object, with only ID added.
You can declare parameters of custom types in sp

CREATE PROCEDURE [dbo].[DoSomethingWithEmployees]
	@IDList AS  dbo.IDList readonly

Example

1. First, in your database, create the following two objects

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

CREATE PROCEDURE [dbo].[DoSomethingWithEmployees]
	@IDList AS  dbo.IDList readonly
	
AS
	 SELECT * FROM [dbo].[Employees] 
	  where ContactId in
	   (  select ID from @IDList )
RETURN 

2. 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
}

Tags: SQL Database

Posted on Tue, 08 Oct 2019 10:35:17 -0700 by Maq