首页 文章

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

提问于
浏览
233

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

例如,我有一份员工清单 . 我想将此列表用作表,并将其与另一个表连接 . 但是员工列表应该作为参数从C#传递 .

9 回答

  • 2

    SQL Server 2008(或更新版本)

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

    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#代码中:

    // 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的拆分功能 . 首先,创建一个函数:

    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
    

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

    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' 传递...


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

    输入是明确定义的(没有人必须猜测分隔符是逗号还是分号)并且我们没有依赖于其他处理函数,如果不检查存储过程的代码则不明显 .

    与涉及用户定义的XML模式而不是UDT的解决方案相比,这涉及相似数量的步骤,但根据我的经验,管理,维护和读取的代码要简单得多 .

    在许多解决方案中,您可能只需要一个或几个这些UDT(用户定义的类型),您可以将它们重复用于许多存储过程 . 与此示例一样,常见的要求是通过ID指针列表,函数名称描述了Ids应该表示的上下文,类型名称应该是通用的 .

  • 5

    根据我的经验,通过从employeeID创建分隔表达式,这个问题有一个棘手且好的解决方案 . 您应该只创建一个字符串表达式,如 ';123;434;365;' in-其中 123434365 是一些employeeID . 通过调用以下过程并将此表达式传递给它,您可以获取所需的记录 . 您可以轻松地将"another table"加入此查询 . 此解决方案适用于所有版本的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
    
  • 6

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

    当您从C#传入它时,您将添加数据类型为SqlDb.Structured的参数 .

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

    例:

    // 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();
    }
    
  • 23

    您需要将其作为XML参数传递 .

    Edit: 来自我的项目的快速代码给你一个想法:

    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)
    

    然后,您可以使用临时表与表连接 . 我们将arrayOfUlong定义为内置XML模式以维护数据完整性,但您不必这样做 . 我建议使用它,所以这里有一个快速的代码,以确保你总是得到一个长的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
    
  • 30

    上下文始终很重要,例如数组的大小和复杂性 . 对于中小型列表,这里发布的几个答案都很好,但应该做出一些澄清:

    • 对于拆分分隔列表,基于SQLCLR的拆分器是最快的 . 有很多例子可以自己编写,或者你可以只下载免费的SQL# CLR函数库(我写过,但String_Split函数,以及许多其他函数完全免费) .

    • 拆分基于XML的数组可能很快,但是您需要使用基于属性的XML,而不是基于元素的XML(这是答案中显示的唯一类型,尽管@AaronBertrand的XML示例是最好的,因为他的代码正在使用 text() XML函数 . 有关使用XML拆分列表的更多信息(即性能分析),请查看Phil Factor的"Using XML to pass lists as parameters in SQL Server" .

    • 使用TVP非常棒(假设您至少使用SQL Server 2008或更新版本),因为数据流式传输到proc并显示为预解析且强类型的表变量 . 但是,在大多数情况下,将所有数据存储在 DataTable 中意味着在从原始集合中复制数据时复制内存中的数据 . 因此,使用传入TVP的 DataTable 方法对于较大的数据集不能很好地工作(即,不能很好地扩展) .

    • XML与Ints或Strings的简单分隔列表不同,可以处理多个一维数组,就像TVP一样 . 但就像 DataTable TVP方法一样,XML也不能很好地扩展,因为它需要将内存中的数据量增加一倍以上另外还要考虑XML文档的开销 .

    尽管如此,如果您使用的数据很大或者数据不是很大但一直在增长,那么 IEnumerable TVP方法是最好的选择,因为它将数据流式传输到SQL Server(如 DataTable 方法),但不是不需要在内存中复制任何集合(与任何其他方法不同) . 我在这个答案中发布了SQL和C#代码的示例:

    Pass Dictionary to Stored Procedure T-SQL

  • 371

    在sql server中不支持数组,但有几种方法可以将集合传递给存储过程 .

    • 使用数据表

    • 使用XML.Try以xml格式转换集合,然后将其作为输入传递给存储过程

    以下链接可以帮助您

    passing collection to a stored procedure

  • 10

    我一直在搜索如何将任何数组传递给sql server的所有示例和答案,而无需创建新的Table类型的麻烦,直到我发现这个linK,下面是我如何将它应用于我的项目:

    • 以下代码将获取一个Array作为参数,并将--array的值插入另一个表中
    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
    

    希望你喜欢它

  • 15

    这会对你有所帮助 . :)按照接下来的步骤,

    • 打开查询设计器

    • 复制粘贴以下代码,它将创建将String转换为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
    
    • 创建以下存储过程
    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
    
    • 执行此SP使用 exec sp_DeleteId '1,2,3,12' 这是您要删除的Id的字符串,

    • 您将数组转换为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 ;
    

    这将删除多行,一切都是最好的

  • 4

    我花了很长时间来解决这个问题,所以如果有人需要它......

    这是基于Aaron的答案中的SQL 2005方法,并使用他的SplitInts函数(我刚刚删除了delim参数,因为我总是使用逗号) . 我正在使用SQL 2008,但我想要一些适用于类型化数据集(XSD,TableAdapters)的东西,我知道字符串参数可以使用它们 .

    我试图让他的函数在“where in(1,2,3)”类型子句中工作,并且没有直接前进的运气 . 所以我首先创建了一个临时表,然后进行了内连接,而不是“在哪里” . 这是我的示例用法,在我的情况下,我想获得一个不包含某些成分的食谱列表:

    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)
    

相关问题