我如何参数化一个包含有可变数量参数的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 2016+中,您可以使用STRING_SPLIT函数:
DECLARE @names NVARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails';
SELECT *
FROM Tags
WHERE Name IN (SELECT [value] FROM STRING_SPLIT(@names, ','))
ORDER BY [Count] DESC;
or:
DECLARE @names NVARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails';
SELECT t.*
FROM Tags t
JOIN STRING_SPLIT(@names,',')
ON t.Name = [value]
ORDER BY [Count] DESC;
现场演示
公认的答案当然是可行的,这是一种方法,但它是反模式的。
E.按值列表查找行 这是对常见反模式的替代,例如在应用层或Transact-SQL中创建动态SQL字符串,或者使用LIKE操作符: 选择ProductId,名称,标签 从产品 ”,1、2、3”,像“%”+投(ProductId VARCHAR(20吗 )) + ',%';
附录:
为了改进STRING_SPLIT表函数行估计,将分离的值实体化为临时表/表变量是一个好主意:
DECLARE @names NVARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails,sql';
CREATE TABLE #t(val NVARCHAR(120));
INSERT INTO #t(val) SELECT s.[value] FROM STRING_SPLIT(@names, ',') s;
SELECT *
FROM Tags tg
JOIN #t t
ON t.val = tg.TagName
ORDER BY [Count] DESC;
现场演示
相关内容:如何将值列表传递给存储过程
原来的问题有要求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#
这是一个可重复使用的变化的解决方案在马克托罗特的优秀答案。
扩展方法:
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);
}
在我看来,解决这个问题的最佳来源是这个网站上发布的内容:
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 ...
...
WHERE tag IN( @tag1, ISNULL( @tag2, @tag1 ), ISNULL( @tag3, @tag1 ), etc. )
有IN('tag1', 'tag2', 'tag1', 'tag1', 'tag1')将很容易被SQL Server优化掉。另外,你可以直接搜索索引
我将传递一个表类型参数(因为它是SQL Server 2008),并做一个where exists,或内部连接。您也可以使用XML,使用sp_xml_preparedocument,然后甚至可以索引临时表。