如何将数组传递到SQL Server存储过程

SQL Server 2008（或更高版本）

首先，在您的数据库中，创建以下两个对象：

CREATE TYPE dbo.IDListAS TABLE(  ID INT);GOCREATE PROCEDURE dbo.DoSomethingWithEmployees  @List AS dbo.IDList READonLYASBEGIN  SET NOCOUNT ON;  SELECt ID FROM @List; ENDGO

现在在您的C＃代码中：

// Obtain your list of ids to send, this is just an example call to a helper utility functionint[] employeeIds = GetEmployeeIds();DataTable tvp = new DataTable();tvp.Columns.Add(new DataColumn("ID", typeof(int)));// populate DataTable from your List hereforeach(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}

SQL Server 2005

如果您使用的是SQL Server 2005，我仍然建议在XML上使用拆分功能。首先，创建一个函数：

CREATE FUNCTION dbo.SplitInts(   @List      VARCHAr(MAX),   @Delimiter VARCHAr(255))RETURNS TABLEAS  RETURN ( SELECt Item = ConVERT(INT, Item) FROM      ( SELECt Item = x.i.value('(./text())[1]', 'varchar(max)')        FROM ( SELECt [XML] = ConVERT(XML, '<i>'        + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')          ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y      WHERe Item IS NOT NULL  );GO

现在，您的存储过程可以是：

CREATE PROCEDURE dbo.DoSomethingWithEmployees  @List VARCHAr(MAX)ASBEGIN  SET NOCOUNT ON;  SELECT EmployeeID = Item FROM dbo.SplitInts(@List, ','); ENDGO

在您的C＃代码中，您只需将列表传递为

'1,2,3,12'

我发现传递表值参数的方法简化了使用它的解决方案的可维护性，并且与包括XML和字符串拆分在内的其他实现相比，通常具有更高的性能。

输入是明确定义的（无需猜测分隔符是逗号还是分号），并且我们不依赖于其他处理功能，这些功能在不检查存储过程的代码的情况下是不明显的。

与涉及用户定义的XML模式而不是UDT的解决方案相比，这涉及到类似的步骤，但是以我的经验，管理，维护和读取的代码要简单得多。

在许多解决方案中，您可能只需要将其中一种或几种UDT（用户定义类型）重新用于许多存储过程。与该示例一样，通常的要求是传递ID指针列表，函数名称描述那些Id应该表示的上下文，类型名称应该是通用的。