这几天我在开玩笑

我正在处理的应用程序对SQL进行了一些高级处理。一种操作是基于集合中的项目名称，从不同的表中选择当前上下文中对象上的各种元数据。为此，执行了一系列" select ... from ... where ... in()"，并且为了防止恶意SQL代码，使用Sql参数构造" in()"子句的内容。

但是，当用于构造" in()"子句的项目集合大于2100个项目时，由于Sql Server对每个查询的最大2100个Sql参数的限制，该操作将失败。

我现在正在尝试的一种方法是创建一个#temp表来存储所有项目名称，然后将该表加入原始查询中，而不是使用" where in()"。这让我抓紧了如何用.NET代码中存储在Array中的项目名称填充表的工作。当然，必须有某种批量方法来插入所有内容，而不是为每个项目发出单独的"插入"？

除此之外，我对解决此问题的替代方法非常感兴趣。

非常感谢

解决方案

Hrm，在不了解上下文以及有关数据以及我们如何使用结果和性能问题的更多信息的情况下，我将尝试提出一种替代方案。我们能否拆分成多个查询？进行与现在相同的操作，但不要构建包含2100+个项目的查询，而是构建两个包含1050个以上的项目，然后合并结果。

一种可能的解决方法是使用查询XML的功能，并简单地将"输入"的所有数据作为xml列发送，然后加入。

可以使用相同的方法来填充临时表，但是再说一遍，为什么不直接使用它呢？

这是一个简短的示例，应说明：

declare @wanted xml
set @wanted = '<ids><id>1</id><id>2</id></ids>'
select * 
from (select 1 Id union all select 3) SourceTable 
where Id in(select Id.value('.', 'int') from @wanted.nodes('/ids/id') as Foo(Id))

只需在应用程序中构建xml并将其作为参数传递即可。

Prevengint恶意SQL代码：>使用存储过程。

是的，SQL Server 2005具有批量插入功能：
http://msdn.microsoft.com/en-us/library/ms188365.aspx

对于批量更新问题：看一下其中有数据表的数据适配器。我们可以设置一个参数，以允许我们批量插入/更新表中的项目，还可以选择一个批次中的项目编号
MSDN文章

似乎我们应该仔细研究业务问题或者领域，以确定更好的方法来过滤查询中的项目。 IN()子句可能不是我们执行此操作的最佳方法。在情况下，也许添加数据类别或者过滤器而不是要包含的较大项目列表会更好。在不了解业务问题/背景的情况下，很难说。

我们可以利用.NET 2.0中引入的SqlBulkCopy类。实际上，它非常易于使用。一探究竟：

http://msdn.microsoft.com/zh-CN/library/system.data.sqlclient.sqlbulkcopy.aspx

好的，我不确定这对我们有什么好处或者性能如何，但是下面是我过去用来实现类似效果的一些代码：

CREATE FUNCTION [dbo].[Split](
    @list ntext
)
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
                          number  int NOT NULL) 
AS
BEGIN
    DECLARE @pos      int,
            @textpos  int,
            @chunklen smallint,
            @str      nvarchar(4000),
            @tmpstr   nvarchar(4000),
            @leftover nvarchar(4000)

    SET @textpos = 1
    SET @leftover = ''
    WHILE @textpos <= datalength(@list) / 2
    BEGIN
       SET @chunklen = 4000 - datalength(@leftover) / 2
       SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen))
       SET @textpos = @textpos + @chunklen

       SET @pos = charindex(',', @tmpstr)
       WHILE @pos > 0
       BEGIN
          SET @str = substring(@tmpstr, 1, @pos - 1)
          INSERT @tbl (number) VALUES(convert(int, @str))
          SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
          SET @pos = charindex(',', @tmpstr)
       END

       SET @leftover = @tmpstr
    END

    IF ltrim(rtrim(@leftover)) <> ''
       INSERT @tbl (number) VALUES(convert(int, @leftover))

    RETURN
END

然后，在其他存储过程中，可以传入以逗号分隔的ID字符串，例如：

select a.number from split('1,2,3') a inner join myothertable b on a.number = b.ID

就像我说的那样，这可能真的很糟糕，因为它包含很多字符串操作，而且我不记得我从哪里获得了函数...但是可以在此处进行选择...

我想如果我们真的不需要索引原始字符串，也可以删除填充listpos列的位。

SQL Server 2008将具有表参数。这是我们想要的锤子。