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

}