如何通过一个数组到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 list of employees. I want to use this list as a table and join it with another table. But list of employees should be passed as parameter from C#.

推荐答案

既然你有一个清单了，我觉得有更简单的方法比XML。

Since you have a List already, I think there are more straightforward ways than XML.

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

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

CREATE TYPE dbo.EmployeeList
AS TABLE
(
  EmployeeID INT
);
GO

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

  SELECT EmployeeID FROM @List; 
END
GO

现在在你的C＃code：

Now in your C# code:

DataTable tvp = new DataTable();
// define / populate DataTable from your List here

using (conn)
{
    SqlCommand cmd = new SqlCommand("dbo.DoSomethingWithEmployees", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter tvparam = cmd.Parameters.AddWithValue("@List", tvp);
    tvparam.SqlDbType = SqlDbType.Structured;
    // 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＃code，你只需要通过名单1,2,3,12 ...

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

我建议你比较对你选择的方法这些选项的可维护性和性能。

I recommend you compare the maintainability and performance of these options against the method you selected.

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