我如何参数化一个包含有可变数量参数的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的优雅方式,我愿意接受。
当前回答
我使用了一个更简洁的投票结果:
List<SqlParameter> parameters = tags.Select((s, i) => new SqlParameter("@tag" + i.ToString(), SqlDbType.NVarChar(50)) { Value = s}).ToList();
var whereCondition = string.Format("tags in ({0})", String.Join(",",parameters.Select(s => s.ParameterName)));
它循环两次标记参数;但大多数时候这并不重要(它不会成为你的瓶颈;如果是,展开循环)。
如果你真的对性能感兴趣,不想重复循环两次,这里有一个不太漂亮的版本:
var parameters = new List<SqlParameter>();
var paramNames = new List<string>();
for (var i = 0; i < tags.Length; i++)
{
var paramName = "@tag" + i;
//Include size and set value explicitly (not AddWithValue)
//Because SQL Server may use an implicit conversion if it doesn't know
//the actual size.
var p = new SqlParameter(paramName, SqlDbType.NVarChar(50) { Value = tags[i]; }
paramNames.Add(paramName);
parameters.Add(p);
}
var inClause = string.Join(",", paramNames);
其他回答
下面是我用过的一个快速而又复杂的技巧:
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标记,否则将无法工作
有些人可能认为还有其他更清洁的方法可以做到这一点,所以请继续阅读。
在SQL SERVER 2016或更高版本中,您可以使用STRING_SPLIT。
DECLARE @InParaSeprated VARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails'
DECLARE @Delimeter VARCHAR(10) = ','
SELECT
*
FROM
Tags T
INNER JOIN STRING_SPLIT(@InputParameters,@Delimeter) SS ON T.Name = SS.value
ORDER BY
Count DESC
我使用这个是因为在我的查询中,有时连接速度比Like操作符快。 此外,您还可以按您喜欢的任何单独格式输入无限数量的数据。 我喜欢这个。
也许我们可以在这里使用XML:
declare @x xml
set @x='<items>
<item myvalue="29790" />
<item myvalue="31250" />
</items>
';
With CTE AS (
SELECT
x.item.value('@myvalue[1]', 'decimal') AS myvalue
FROM @x.nodes('//items/item') AS x(item) )
select * from YourTable where tableColumnName in (select myvalue from cte)
唯一的制胜方法就是不玩。
对你来说没有无限的可变性。只有有限的可变性。
在SQL中,你有这样一个子句:
and ( {1}==0 or b.CompanyId in ({2},{3},{4},{5},{6}) )
在c#代码中,你可以这样做:
int origCount = idList.Count;
if (origCount > 5) {
throw new Exception("You may only specify up to five originators to filter on.");
}
while (idList.Count < 5) { idList.Add(-1); } // -1 is an impossible value
return ExecuteQuery<PublishDate>(getValuesInListSQL,
origCount,
idList[0], idList[1], idList[2], idList[3], idList[4]);
基本上,如果count为0,那么就没有过滤器,所有东西都会通过。如果计数高于0,则该值必须在列表中,但列表已被填充为5个不可能的值(因此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的好文章——都是快速而干净的。