我如何参数化一个包含有可变数量参数的IN子句的查询,就像这样?

SELECT * FROM Tags 
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC

在这个查询中,参数的数量可以是1到5之间的任意值。

我不喜欢使用专门的存储过程(或XML),但如果有一些特定于SQL Server 2008的优雅方式,我愿意接受。


当前回答

另一种可能的解决方案是,不向存储过程传递可变数量的参数,而是传递一个包含您要查找的名称的字符串,但通过将它们围绕在'<>'中使它们唯一。然后使用PATINDEX查找名称:

SELECT * 
FROM Tags 
WHERE PATINDEX('%<' + Name + '>%','<jo>,<john>,<scruffy>,<rubyonrails>') > 0

其他回答

在我看来,解决这个问题的最佳来源是这个网站上发布的内容:

Syscomments。Dinakar Nethi

CREATE FUNCTION dbo.fnParseArray (@Array VARCHAR(1000),@separator CHAR(1))
RETURNS @T Table (col1 varchar(50))
AS 
BEGIN
 --DECLARE @T Table (col1 varchar(50))  
 -- @Array is the array we wish to parse
 -- @Separator is the separator charactor such as a comma
 DECLARE @separator_position INT -- This is used to locate each separator character
 DECLARE @array_value VARCHAR(1000) -- this holds each array value as it is returned
 -- For my loop to work I need an extra separator at the end. I always look to the
 -- left of the separator character for each array value

 SET @array = @array + @separator

 -- Loop through the string searching for separtor characters
 WHILE PATINDEX('%' + @separator + '%', @array) <> 0 
 BEGIN
    -- patindex matches the a pattern against a string
    SELECT @separator_position = PATINDEX('%' + @separator + '%',@array)
    SELECT @array_value = LEFT(@array, @separator_position - 1)
    -- This is where you process the values passed.
    INSERT into @T VALUES (@array_value)    
    -- Replace this select statement with your processing
    -- @array_value holds the value of this element of the array
    -- This replaces what we just processed with and empty string
    SELECT @array = STUFF(@array, 1, @separator_position, '')
 END
 RETURN 
END

Use:

SELECT * FROM dbo.fnParseArray('a,b,c,d,e,f', ',')

致谢:Dinakar Nethi

下面是我用过的一个快速而又复杂的技巧:

SELECT * FROM Tags
WHERE '|ruby|rails|scruffy|rubyonrails|'
LIKE '%|' + Name + '|%'

下面是c#代码:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
const string cmdText = "select * from tags where '|' + @tags + '|' like '%|' + Name + '|%'";

using (SqlCommand cmd = new SqlCommand(cmdText)) {
   cmd.Parameters.AddWithValue("@tags", string.Join("|", tags);
}

两个问题:

演出糟透了。像“%……%"查询没有索引。 请确保没有任何|、blank或null标记,否则将无法工作

有些人可能认为还有其他更清洁的方法可以做到这一点,所以请继续阅读。

我听到Jeff/Joel今天在播客上谈论这个(第34集,2008-12-16 (MP3, 31 MB), 1小时03分38秒-1小时06分45秒),我想我记得Stack Overflow使用LINQ到SQL,但也许它被抛弃了。在LINQ to SQL中也是如此。

var inValues = new [] { "ruby","rails","scruffy","rubyonrails" };

var results = from tag in Tags
              where inValues.Contains(tag.Name)
              select tag;

就是这样。而且,是的,LINQ已经足够向后看了,但是Contains子句对我来说似乎太向后看了。当我不得不在工作中为一个项目做类似的查询时,我自然地试图用错误的方式来做这个,在本地数组和SQL Server表之间做一个连接,认为LINQ to SQL翻译器将足够聪明,以某种方式处理翻译。它没有这样做,但它确实提供了一个描述性的错误消息,并指示我使用Contains。

无论如何,如果您在强烈推荐的LINQPad中运行此命令,并运行此查询,您可以查看SQL LINQ提供程序生成的实际SQL。它将向您显示每个参数化为IN子句的值。

你可以参数化每个值,就像这样:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
string cmdText = "SELECT * FROM Tags WHERE Name IN ({0})";

string[] paramNames = tags.Select(
    (s, i) => "@tag" + i.ToString()
).ToArray();

string inClause = string.Join(", ", paramNames);
using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause))) {
    for(int i = 0; i < paramNames.Length; i++) {
       cmd.Parameters.AddWithValue(paramNames[i], tags[i]);
    }
}

这将给你:

cmd.CommandText = "SELECT * FROM Tags WHERE Name IN (@tag0, @tag1, @tag2, @tag3)"
cmd.Parameters["@tag0"] = "ruby"
cmd.Parameters["@tag1"] = "rails"
cmd.Parameters["@tag2"] = "scruffy"
cmd.Parameters["@tag3"] = "rubyonrails"

不,这不是对SQL注入开放的。唯一注入到CommandText中的文本不是基于用户输入的。它完全基于硬编码的“@tag”前缀和数组的索引。索引总是一个整数,不是用户生成的,并且是安全的。

用户输入的值仍然被填充到参数中,因此不存在漏洞。

编辑:

除了注入问题外,要注意构造命令文本以容纳可变数量的参数(如上所述)会阻碍SQL服务器利用缓存查询的能力。最终的结果是,您几乎肯定会在第一时间失去使用参数的价值(而不是仅仅将谓词字符串插入SQL本身)。

并不是说缓存的查询计划没有价值,但在我看来,这个查询还没有复杂到可以从中看到很多好处。虽然编译成本可能接近(甚至超过)执行成本,但仍然是毫秒级的。

如果你有足够的RAM,我希望SQL Server也能缓存一个用于常见参数计数的计划。我认为你总是可以添加五个参数,并让未指定的标签为NULL -查询计划应该是相同的,但这对我来说似乎很难看,我不确定它是否值得进行微观优化(尽管,在Stack Overflow上-它可能非常值得)。

此外,SQL Server 7及后续版本将自动参数化查询,因此从性能的角度来看,使用参数并不是真正必要的——然而,从安全的角度来看,它是至关重要的——特别是对于这样的用户输入数据。

这可能是一种有点讨厌的方法,我用过一次,相当有效。

根据你的目标,它可能会有用。

创建一个只有一列的临时表。 将每个查找值插入到该列中。 不使用IN,只需使用标准JOIN规则。(灵活性++)

这为您所能做的事情提供了一些额外的灵活性,但它更适合这样的情况:需要查询一个大型表,有良好的索引,并且希望多次使用参数化列表。节省了执行两次,所有的卫生工作都是手动完成的。

我从来没有时间去分析它到底有多快,但在我的情况下,它是需要的。