我如何参数化一个包含有可变数量参数的IN子句的查询，就像这样?
SELECT * FROM Tags
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC
在这个查询中，参数的数量可以是1到5之间的任意值。
我不喜欢使用专门的存储过程(或XML)，但如果有一些特定于SQL Server 2008的优雅方式，我愿意接受。
当前回答
请使用以下存储过程。它使用了一个自定义分割函数，可以在这里找到。
create stored procedure GetSearchMachingTagNames
@PipeDelimitedTagNames varchar(max),
@delimiter char(1)
as
begin
select * from Tags
where Name in (select data from [dbo].[Split](@PipeDelimitedTagNames,@delimiter)
end
其他回答
这可能是一种有点讨厌的方法，我用过一次，相当有效。
根据你的目标，它可能会有用。
创建一个只有一列的临时表。 将每个查找值插入到该列中。 不使用IN，只需使用标准JOIN规则。(灵活性++)
这为您所能做的事情提供了一些额外的灵活性，但它更适合这样的情况:需要查询一个大型表，有良好的索引，并且希望多次使用参数化列表。节省了执行两次，所有的卫生工作都是手动完成的。
我从来没有时间去分析它到底有多快，但在我的情况下，它是需要的。
下面是我用过的一个快速而又复杂的技巧:
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标记，否则将无法工作
有些人可能认为还有其他更清洁的方法可以做到这一点，所以请继续阅读。
create FUNCTION [dbo].[ConvertStringToList]
(@str VARCHAR (MAX), @delimeter CHAR (1))
RETURNS
@result TABLE (
[ID] INT NULL)
AS
BEG
IN
DECLARE @x XML
SET @x = '<t>' + REPLACE(@str, @delimeter, '</t><t>') + '</t>'
INSERT INTO @result
SELECT DISTINCT x.i.value('.', 'int') AS token
FROM @x.nodes('//t') x(i)
ORDER BY 1
RETURN
END
——你的查询
select * from table where id in ([dbo].[ConvertStringToList(YOUR comma separated string ,',')])
在ColdFusion中，我们只需要:
<cfset myvalues = "ruby|rails|scruffy|rubyonrails">
<cfquery name="q">
select * from sometable where values in <cfqueryparam value="#myvalues#" list="true">
</cfquery>
另一种可能的解决方案是，不向存储过程传递可变数量的参数，而是传递一个包含您要查找的名称的字符串，但通过将它们围绕在'<>'中使它们唯一。然后使用PATINDEX查找名称:
SELECT *
FROM Tags
WHERE PATINDEX('%<' + Name + '>%','<jo>,<john>,<scruffy>,<rubyonrails>') > 0
