参数化的Sql查询

本文关键字：查询 Sql 参数 | 更新日期: 2023-09-27 17:47:24

这是我最近正在破解的一个坚果

我正在开发的应用程序对SQL有一些高级处理。其中一个操作根据集合中的项目名称，从不同的表中选择当前上下文中对象的各种元数据。为此，执行一系列的"select…from…where…in（）"，为了防止恶意SQL代码，SQL参数用于构造"in（））"子句的内容。

但是，当用于构造"in（）"子句的项集合大于2100个项时，由于Sql Server对每个查询最多2100个Sql参数的限制，这将失败。

我现在尝试的一种方法是创建一个#temp表来存储所有项目名称，然后将该表连接到原始查询中，而不是使用"where in（）"。这让我很难理解如何用.NET代码中存储在数组中的项名称填充表。当然，必须有一些批量的方式来插入所有内容，而不是为每个项目单独发布"插入"？

除此之外，我对解决这个问题的其他方法非常感兴趣。

非常感谢

参数化的Sql查询

一个潜在的解决方法是使用查询XML的功能，只需将"in"的所有数据作为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并将其作为参数传递即可。

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

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

是的，SQLServer2005有一个大容量插入：http://msdn.microsoft.com/en-us/library/ms188365.aspx

您可以使用.NET 2.0中引入的SqlBulkCopy类。它实际上使用起来非常简单。看看：

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

对于批量更新问题：看看数据适配器中有一个数据表。你可以设置一个参数，允许你批量插入/更新表中的项，你可以选择批量中的项数MSDN文章

您似乎应该仔细查看业务问题或域，以确定筛选查询中项目的更好方法。IN（）子句可能不是实现此目的的最佳方法。也许在您的情况下，添加数据类别或过滤器而不是要包含的大项目列表会更好。如果不了解更多关于业务问题/背景的信息，就很难说。

好吧，我不确定这对你有多好，也不确定它的性能如何，但这里有一些我过去用来实现类似功能的代码：

    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将具有表参数。这就是你想要的锤子。

相关文章：