SQL Server 存储过程 数组参数 （How to pass an array into a SQL Server stored procedure）

resource from *

使用存储过程，如何传递数组参数？

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
}