SQL Server 存儲過程 數組參數 （How to pass an array into a SQL Server stored procedure）

Resource from StackOverflow

使用存儲過程，如何傳遞數組參數？

1.分割解析字元串，太麻煩
2.添加Sql Server 自定義類型 sp_addtype

問題需求：需要向SP 傳遞數組類型的參數

select * from Users where ID IN (1,2,3 )

Sql Server 數據類型 並沒有數組，但是允許自定義類型，通過 sp_addtype
添加 一個自定義的數據類型，可以允許c# code 向sp傳遞 一個數組類型的參數
但是不能直接使用 sp_addtype，而是需要結構類型的數據格式，如下：

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

有點像個是一個臨時表，一種對象，這裡只加了ID
在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
}