我如何参数化一个包含有可变数量参数的IN子句的查询,就像这样?
SELECT * FROM Tags
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC
在这个查询中,参数的数量可以是1到5之间的任意值。
我不喜欢使用专门的存储过程(或XML),但如果有一些特定于SQL Server 2008的优雅方式,我愿意接受。
我如何参数化一个包含有可变数量参数的IN子句的查询,就像这样?
SELECT * FROM Tags
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC
在这个查询中,参数的数量可以是1到5之间的任意值。
我不喜欢使用专门的存储过程(或XML),但如果有一些特定于SQL Server 2008的优雅方式,我愿意接受。
当前回答
我将传递一个表类型参数(因为它是SQL Server 2008),并做一个where exists,或内部连接。您也可以使用XML,使用sp_xml_preparedocument,然后甚至可以索引临时表。
其他回答
这很恶心,但如果你保证至少有一个,你可以这样做:
SELECT ...
...
WHERE tag IN( @tag1, ISNULL( @tag2, @tag1 ), ISNULL( @tag3, @tag1 ), etc. )
有IN('tag1', 'tag2', 'tag1', 'tag1', 'tag1')将很容易被SQL Server优化掉。另外,你可以直接搜索索引
对于这样数量可变的参数,我所知道的唯一方法是显式地生成SQL,或者做一些涉及用所需项填充临时表并与临时表连接的事情。
这是一个解决同样问题的交叉帖子。比保留分隔符更健壮-包括转义和嵌套数组,并理解null和空数组。
c# & T-SQL string[]打包/解包实用函数
然后可以连接到表值函数。
这是一个可重复使用的变化的解决方案在马克托罗特的优秀答案。
扩展方法:
public static class ParameterExtensions
{
public static Tuple<string, SqlParameter[]> ToParameterTuple<T>(this IEnumerable<T> values)
{
var createName = new Func<int, string>(index => "@value" + index.ToString());
var paramTuples = values.Select((value, index) =>
new Tuple<string, SqlParameter>(createName(index), new SqlParameter(createName(index), value))).ToArray();
var inClause = string.Join(",", paramTuples.Select(t => t.Item1));
var parameters = paramTuples.Select(t => t.Item2).ToArray();
return new Tuple<string, SqlParameter[]>(inClause, parameters);
}
}
用法:
string[] tags = {"ruby", "rails", "scruffy", "rubyonrails"};
var paramTuple = tags.ToParameterTuple();
var cmdText = $"SELECT * FROM Tags WHERE Name IN ({paramTuple.Item1})";
using (var cmd = new SqlCommand(cmdText))
{
cmd.Parameters.AddRange(paramTuple.Item2);
}
下面是我用过的一个快速而又复杂的技巧:
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标记,否则将无法工作
有些人可能认为还有其他更清洁的方法可以做到这一点,所以请继续阅读。