我如何参数化一个包含有可变数量参数的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的优雅方式,我愿意接受。
当前回答
您可以通过以下方法以可重用的方式完成此任务-
public static class SqlWhereInParamBuilder
{
public static string BuildWhereInClause<t>(string partialClause, string paramPrefix, IEnumerable<t> parameters)
{
string[] parameterNames = parameters.Select(
(paramText, paramNumber) => "@" + paramPrefix + paramNumber.ToString())
.ToArray();
string inClause = string.Join(",", parameterNames);
string whereInClause = string.Format(partialClause.Trim(), inClause);
return whereInClause;
}
public static void AddParamsToCommand<t>(this SqlCommand cmd, string paramPrefix, IEnumerable<t> parameters)
{
string[] parameterValues = parameters.Select((paramText) => paramText.ToString()).ToArray();
string[] parameterNames = parameterValues.Select(
(paramText, paramNumber) => "@" + paramPrefix + paramNumber.ToString()
).ToArray();
for (int i = 0; i < parameterNames.Length; i++)
{
cmd.Parameters.AddWithValue(parameterNames[i], parameterValues[i]);
}
}
}
要了解更多细节,请参阅这篇博客文章-参数化SQL WHERE IN子句c#
其他回答
这很恶心,但如果你保证至少有一个,你可以这样做:
SELECT ...
...
WHERE tag IN( @tag1, ISNULL( @tag2, @tag1 ), ISNULL( @tag3, @tag1 ), etc. )
有IN('tag1', 'tag2', 'tag1', 'tag1', 'tag1')将很容易被SQL Server优化掉。另外,你可以直接搜索索引
对于这样数量可变的参数,我所知道的唯一方法是显式地生成SQL,或者做一些涉及用所需项填充临时表并与临时表连接的事情。
有一个很好的、简单的、经过测试的方法:
/* Create table-value string: */
CREATE TYPE [String_List] AS TABLE ([Your_String_Element] varchar(max) PRIMARY KEY);
GO
/* Create procedure which takes this table as parameter: */
CREATE PROCEDURE [dbo].[usp_ListCheck]
@String_List_In [String_List] READONLY
AS
SELECT a.*
FROM [dbo].[Tags] a
JOIN @String_List_In b ON a.[Name] = b.[Your_String_Element];
我已经开始使用这种方法来修复实体框架的问题(对于我们的应用程序来说不够健壮)。所以我们决定给Dapper(和Stack一样)一个机会。还指定您的字符串列表为表与PK列修复您的执行计划很多。 这是一篇关于如何将一个表转换为Dapper的好文章——都是快速而干净的。
这是一个可重复使用的变化的解决方案在马克托罗特的优秀答案。
扩展方法:
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);
}
这可能是一种有点讨厌的方法,我用过一次,相当有效。
根据你的目标,它可能会有用。
创建一个只有一列的临时表。 将每个查找值插入到该列中。 不使用IN,只需使用标准JOIN规则。(灵活性++)
这为您所能做的事情提供了一些额外的灵活性,但它更适合这样的情况:需要查询一个大型表,有良好的索引,并且希望多次使用参数化列表。节省了执行两次,所有的卫生工作都是手动完成的。
我从来没有时间去分析它到底有多快,但在我的情况下,它是需要的。