如何将数组传递到 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(用户定义类型)中的一个或几个，这些 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屋！