如何将一个数组传递到一个SQL Server存储过程中?

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

如何将数组传递到SQL服务器存储过程中?

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#.

例如，我有一个员工列表。我想把这个列表用作一个表，并将它与另一个表连接起来。但是员工列表应该作为参数从c#中传递。

9 个解决方案

#1

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

既然您已经有了一个列表，我认为还有比XML更简单的方法。

SQL Server 2008 (or newer)

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

Now in your C# code:

现在在你的c#代码:

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

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

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

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

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

在你的c#代码中，你只需要将列表传递给“1,2,3,12”…

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

我建议您将这些选项的可维护性和性能与您选择的方法进行比较。

#2

Based on my experience, by creating a delimited expression from the employeeIDs, there is a tricky and nice solution for this problem. You should only create an string expression like ';123;434;365;' in-which 123, 434 and 365 are some employeeIDs. By calling the below procedure and passing this expression to it, you can fetch your desired records. Easily you can join the "another table" into this query. This solution is suitable in all versions of SQL server. Also, in comparison with using table variable or temp table, it is very faster and optimized solution.

根据我的经验，通过从雇员创建一个带分隔符的表达式，有一个棘手的、漂亮的解决方案。您应该只创建一个字符串表达式，如';123;434;365;'，其中123、434和365是一些employee。通过调用下面的过程并将这个表达式传递给它，您可以获取所需的记录。您可以轻松地将“另一个表”连接到此查询中。此解决方案适用于所有版本的SQL server。此外，与使用表变量或临时表相比，它是非常快且优化的解决方案。

CREATE PROCEDURE dbo.DoSomethingOnSomeEmployees  @List AS varchar(max)
AS
BEGIN
  SELECT EmployeeID 
  FROM EmployeesTable
  -- inner join AnotherTable on ...
  where @List like '%;'+cast(employeeID as varchar(20))+';%'
END
GO

#3

Use a table-valued parameter for your stored procedure.

为存储过程使用表值参数。

When you pass it in from C# you'll add the parameter with the data type of SqlDb.Structured.

当您从c#中传递它时，您将添加具有sqldb .结构化数据类型的参数。

See here: http://msdn.microsoft.com/en-us/library/bb675163.aspx

在这里看到的:http://msdn.microsoft.com/en-us/library/bb675163.aspx

Example:

例子:

// Assumes connection is an open SqlConnection object.
using (connection)
{
// Create a DataTable with the modified rows.
DataTable addedCategories =
  CategoriesDataTable.GetChanges(DataRowState.Added);

// Configure the SqlCommand and SqlParameter.
SqlCommand insertCommand = new SqlCommand(
    "usp_InsertCategories", connection);
insertCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(
    "@tvpNewCategories", addedCategories);
tvpParam.SqlDbType = SqlDbType.Structured;

// Execute the command.
insertCommand.ExecuteNonQuery();
}

#4

You need to pass it as an XML parameter.

您需要将它作为XML参数传递。

Edit: quick code from my project to give you an idea:

编辑:快速代码从我的项目给你一个想法:

CREATE PROCEDURE [dbo].[GetArrivalsReport]
    @DateTimeFrom AS DATETIME,
    @DateTimeTo AS DATETIME,
    @HostIds AS XML(xsdArrayOfULong)
AS
BEGIN
    DECLARE @hosts TABLE (HostId BIGINT)

    INSERT INTO @hosts
        SELECT arrayOfUlong.HostId.value('.','bigint') data
        FROM @HostIds.nodes('/arrayOfUlong/u') as arrayOfUlong(HostId)

Then you can use the temp table to join with your tables. We defined arrayOfUlong as a built in XML schema to maintain data integrity, but you don't have to do that. I'd recommend using it so here's a quick code for to make sure you always get an XML with longs.

然后，您可以使用临时表来连接您的表。我们将arrayOfUlong定义为内置的XML模式，以维护数据完整性，但您不必这样做。我建议使用它，所以这里有一个快速代码，以确保您总是能得到一个带有longs的XML。

IF NOT EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'xsdArrayOfULong')
BEGIN
    CREATE XML SCHEMA COLLECTION [dbo].[xsdArrayOfULong]
    AS N'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="arrayOfUlong">
        <xs:complexType>
            <xs:sequence>
                <xs:element maxOccurs="unbounded"
                            name="u"
                            type="xs:unsignedLong" />
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>';
END
GO

#5

Context is always important, such as the size and complexity of the array. For small to mid-size lists, several of the answers posted here are just fine, though some clarifications should be made:

上下文总是很重要的，比如数组的大小和复杂性。对于小到中等大小的列表，这里列出的一些答案是可以的，但是应该做一些澄清:

• For splitting a delimited list, a SQLCLR-based splitter is the fastest. There are numerous examples around if you want to write your own, or you can just download the free SQL# library of CLR functions (which I wrote, but the String_Split function, and many others, are completely free).
• 对于分隔带分隔符的列表，基于sqlclr的拆分器是最快的。如果您想编写自己的代码，或者您可以下载CLR函数的免费SQL#库(我编写了这个库，但是String_Split函数和其他许多函数都是完全免费的)，则可以使用大量的示例。
• Splitting XML-based arrays can be fast, but you need to use attribute-based XML, not element-based XML (which is the only type shown in the answers here, though @AaronBertrand's XML example is the best as his code is using the text() XML function. For more info (i.e. performance analysis) on using XML to split lists, check out "Using XML to pass lists as parameters in SQL Server" by Phil Factor.
• 分割基于XML的数组可以很快，但是您需要使用基于属性的XML，而不是基于元素的XML(这是这里答案中显示的唯一类型，尽管@AaronBertrand的XML示例是最好的，因为他的代码使用的是text() XML函数。要了解更多的信息(即性能分析)，请参阅“使用XML将列表作为SQL Server中的参数”。
• Using TVPs is great (assuming you are using at least SQL Server 2008, or newer) as the data is streamed to the proc and shows up pre-parsed and strongly-typed as a table variable. HOWEVER, in most cases, storing all of the data in DataTable means duplicating the data in memory as it is copied from the original collection. Hence using the DataTable method of passing in TVPs does not work well for larger sets of data (i.e. does not scale well).
• 使用TVPs很好(假设您至少使用SQL Server 2008或更新版本)，因为数据流到proc并显示预解析和强类型的表变量。但是，在大多数情况下，将所有数据存储在DataTable中意味着将数据复制到内存中，因为数据是从原始集合中复制的。因此，使用传输到TVPs的数据表方法不能很好地处理更大的数据集(即不能很好地扩展)。
• XML, unlike simple delimited lists of Ints or Strings, can handle more than one-dimensional arrays, just like TVPs. But also just like the DataTable TVP method, XML does not scale well as it more than doubles the datasize in memory as it needs to additionally account for the overhead of the XML document.
• XML不像简单的带分隔符的int或string列表，它可以处理一维数组，就像TVPs一样。但是，与DataTable TVP方法一样，XML也不能很好地扩展，因为它需要额外地考虑XML文档的开销，从而使内存中的数据量增加一倍以上。

With all of that said, IF the data you are using is large or is not very large yet but consistently growing, then the IEnumerable TVP method is the best choice as it streams the data to SQL Server (like the DataTable method), BUT doesn't require any duplication of the collection in memory (unlike any of the other methods). I posted an example of the SQL and C# code in this answer:

的说,如果您正在使用的数据是大或不是非常大,但持续增长,然后IEnumerable利用方法是最好的选择,因为它流数据的SQL服务器(比如DataTable方法),但是不需要任何重复的集合在内存中(不像其他方法)。我在这个回答中发布了一个SQL和c#代码示例:

Pass Dictionary to Stored Procedure T-SQL

将字典传递给存储过程T-SQL

#6

There is no support for array in sql server but there are several ways by which you can pass collection to a stored proc .

在sql server中没有对数组的支持，但是有几种方法可以将集合传递给存储的proc。

1. By using datatable
2. 通过使用数据表
3. By using XML.Try converting your collection in an xml format and then pass it as an input to a stored procedure
4. 通过使用XML。尝试以xml格式转换集合，然后将其作为输入传递给存储过程

The below link may help you

下面的链接可以帮助你

passing collection to a stored procedure

将集合传递给存储过程

#7

I've been searching through all the examples and answers of how to pass any array to sql server without the hassle of creating new Table type,till i found this linK, below is how I applied it to my project:

我搜索了所有的例子和答案，关于如何将任何数组传递给sql server而不需要创建新的表类型，直到找到这个链接，下面是我如何将它应用到我的项目:

--The following code is going to get an Array as Parameter and insert the values of that --array into another table

——下面的代码将获取一个数组作为参数，并将该数组的值——数组插入到另一个表中

Create Procedure Proc1 


@UserId int, //just an Id param
@s nvarchar(max)  //this is the array your going to pass from C# code to your Sproc

AS

    declare @xml xml

    set @xml = N'<root><r>' + replace(@s,',','</r><r>') + '</r></root>'

    Insert into UserRole (UserID,RoleID)
    select 
       @UserId [UserId], t.value('.','varchar(max)') as [RoleId]


    from @xml.nodes('//root/r') as a(t)
END 

Hope you enjoy it

希望你享受它

#8

This will help you. :) Follow the next steps,

这将帮助你。:)按照以下步骤，

1. Open the Query Designer
2. 打开查询设计器

3. Copy Paste the Following code as it is,it will create the Function which convert the String to Int

   复制粘贴如下代码，它将创建将字符串转换为Int的函数

   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
   

4. Create the Following stored procedure

   创建以下存储过程。

    CREATE PROCEDURE dbo.sp_DeleteMultipleId
    @List VARCHAR(MAX)
    AS
    BEGIN
         SET NOCOUNT ON;
         DELETE FROM TableName WHERE Id IN( SELECT Id = Item FROM dbo.SplitInts(@List, ',')); 
    END
    GO
   

5. Execute this SP Using exec sp_DeleteId '1,2,3,12' this is a string of Id's which you want to delete,

   使用exec sp_DeleteId '1,2,3,12'执行这个SP，这是一个你想要删除的Id字符串，

6. You convert your array to string in C# and pass it as a Stored Procedure parameter

   您将数组转换为c#中的字符串，并将其作为存储过程参数传递

   int[] intarray = { 1, 2, 3, 4, 5 };  
   string[] result = intarray.Select(x=>x.ToString()).ToArray();
   

    

    

   SqlCommand command = new SqlCommand();
   command.Connection = connection;
   command.CommandText = "sp_DeleteMultipleId";
   command.CommandType = CommandType.StoredProcedure;
   command.Parameters.Add("@Id",SqlDbType.VARCHAR).Value=result ;
   

This will delete multiple rows, All the best

这将删除多个行，这是最好的

#9

It took me a long time to figure this out, so in case anyone needs it...

我花了很长时间才弄明白，所以万一有人需要……

This is based on the SQL 2005 method in Aaron's answer, and using his SplitInts function (I just removed the delim param since I'll always use commas). I'm using SQL 2008 but I wanted something that works with typed datasets (XSD, TableAdapters) and I know string params work with those.

这是基于Aaron的SQL 2005方法，并使用了他的SplitInts函数(我刚刚删除了delim param，因为我总是使用逗号)。我正在使用SQL 2008，但是我想要一些与类型化数据集(XSD、tableadapter)一起工作的东西，而且我知道字符串params可以处理这些数据集。

I was trying to get his function to work in a "where in (1,2,3)" type clause, and having no luck the straight-forward way. So I created a temp table first, and then did an inner join instead of the "where in". Here is my example usage, in my case I wanted to get a list of recipes that don't contain certain ingredients:

我试着让他的函数在“where in(1,2,3)”类型的子句中工作，没有运气的直接方式。所以我首先创建了一个临时表，然后做了一个内部连接，而不是“where in”。下面是我的示例用法，在我的例子中，我想要得到一份不含特定成分的食谱列表:

CREATE PROCEDURE dbo.SOExample1
    (
    @excludeIngredientsString varchar(MAX) = ''
    )
AS
    /* Convert string to table of ints */
    DECLARE @excludeIngredients TABLE (ID int)
    insert into @excludeIngredients
    select ID = Item from dbo.SplitInts(@excludeIngredientsString)

    /* Select recipies that don't contain any ingredients in our excluded table */
   SELECT        r.Name, r.Slug
FROM            Recipes AS r LEFT OUTER JOIN
                         RecipeIngredients as ri inner join
                         @excludeIngredients as ei on ri.IngredientID = ei.ID
                         ON r.ID = ri.RecipeID
WHERE        (ri.RecipeID IS NULL)