如何将数组传递到SQL Server存储过程 [英] How to pass an array into a SQL Server stored procedure

问题描述

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

How to pass an array into a SQL Server stored procedure?

例如，我有一个雇员列表。我想将此列表用作表并将其与另一个表连接。但是，应从C＃中将雇员列表作为参数传递。

For example, I have a list of employees. I want to use this list as a table and join it with another table. But the list of employees should be passed as parameter from C#.

推荐答案

SQL Server 2008（或更高版本）

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

SQL Server 2008 (or newer)

First, in your database, create the following two objects:

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

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List AS dbo.IDList READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT ID FROM @List; 
END
GO

现在在您的C＃代码中：

Now 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
}

SQL Server 2005

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

SQL Server 2005

If you are using SQL Server 2005, I would still recommend a split function over XML. First, create a function:

CREATE FUNCTION dbo.SplitInts
(
   @List      VARCHAR(MAX),
   @Delimiter VARCHAR(255)
)
RETURNS TABLE
AS
  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

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

Now your stored procedure can just be:

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List VARCHAR(MAX)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT EmployeeID = Item FROM dbo.SplitInts(@List, ','); 
END
GO

在C＃代码中，您只需要传递列为'1,2,3,12' ...

And in your C# code you just have to pass the list as '1,2,3,12'...

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

I find the method of passing through table valued parameters simplifies the maintainability of a solution that uses it and often has increased performance compared to other implementations including XML and string splitting.

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

The inputs are clearly defined (no one has to guess if the delimiter is a comma or a semi-colon) and we do not have dependencies on other processing functions that are not obvious without inspecting the code for the stored procedure.

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

Compared to solutions involving user defined XML schema instead of UDTs, this involves a similar number of steps but in my experience is far simpler code to manage, maintain and read.

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

In many solutions you may only need one or a few of these UDTs (User defined Types) that you re-use for many stored procedures. As with this example, the common requirement is to pass through a list of ID pointers, the function name describes what context those Ids should represent, the type name should be generic.

这篇关于如何将数组传递到SQL Server存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！