我如何参数化一个包含有可变数量参数的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的优雅方式,我愿意接受。
当前回答
另一种可能的解决方案是,不向存储过程传递可变数量的参数,而是传递一个包含您要查找的名称的字符串,但通过将它们围绕在'<>'中使它们唯一。然后使用PATINDEX查找名称:
SELECT *
FROM Tags
WHERE PATINDEX('%<' + Name + '>%','<jo>,<john>,<scruffy>,<rubyonrails>') > 0
其他回答
使用动态查询。前端只生成所需的格式:
DECLARE @invalue VARCHAR(100)
SELECT @invalue = '''Bishnu'',''Gautam'''
DECLARE @dynamicSQL VARCHAR(MAX)
SELECT @dynamicSQL = 'SELECT * FROM #temp WHERE [name] IN (' + @invalue + ')'
EXEC (@dynamicSQL)
SQL小提琴
你可以参数化每个值,就像这样:
string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
string cmdText = "SELECT * FROM Tags WHERE Name IN ({0})";
string[] paramNames = tags.Select(
(s, i) => "@tag" + i.ToString()
).ToArray();
string inClause = string.Join(", ", paramNames);
using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause))) {
for(int i = 0; i < paramNames.Length; i++) {
cmd.Parameters.AddWithValue(paramNames[i], tags[i]);
}
}
这将给你:
cmd.CommandText = "SELECT * FROM Tags WHERE Name IN (@tag0, @tag1, @tag2, @tag3)"
cmd.Parameters["@tag0"] = "ruby"
cmd.Parameters["@tag1"] = "rails"
cmd.Parameters["@tag2"] = "scruffy"
cmd.Parameters["@tag3"] = "rubyonrails"
不,这不是对SQL注入开放的。唯一注入到CommandText中的文本不是基于用户输入的。它完全基于硬编码的“@tag”前缀和数组的索引。索引总是一个整数,不是用户生成的,并且是安全的。
用户输入的值仍然被填充到参数中,因此不存在漏洞。
编辑:
除了注入问题外,要注意构造命令文本以容纳可变数量的参数(如上所述)会阻碍SQL服务器利用缓存查询的能力。最终的结果是,您几乎肯定会在第一时间失去使用参数的价值(而不是仅仅将谓词字符串插入SQL本身)。
并不是说缓存的查询计划没有价值,但在我看来,这个查询还没有复杂到可以从中看到很多好处。虽然编译成本可能接近(甚至超过)执行成本,但仍然是毫秒级的。
如果你有足够的RAM,我希望SQL Server也能缓存一个用于常见参数计数的计划。我认为你总是可以添加五个参数,并让未指定的标签为NULL -查询计划应该是相同的,但这对我来说似乎很难看,我不确定它是否值得进行微观优化(尽管,在Stack Overflow上-它可能非常值得)。
此外,SQL Server 7及后续版本将自动参数化查询,因此从性能的角度来看,使用参数并不是真正必要的——然而,从安全的角度来看,它是至关重要的——特别是对于这样的用户输入数据。
如果在IN子句中存储了以逗号(,)分隔的字符串,则可以使用charindex函数获取值。如果使用. net,则可以使用SqlParameters进行映射。
DDL Script:
CREATE TABLE Tags
([ID] int, [Name] varchar(20))
;
INSERT INTO Tags
([ID], [Name])
VALUES
(1, 'ruby'),
(2, 'rails'),
(3, 'scruffy'),
(4, 'rubyonrails')
;
t - sql:
DECLARE @Param nvarchar(max)
SET @Param = 'ruby,rails,scruffy,rubyonrails'
SELECT * FROM Tags
WHERE CharIndex(Name,@Param)>0
您可以在. net代码中使用上述语句并将参数映射为SqlParameter。
提琴手演示
编辑: 使用下面的脚本创建名为SelectedTags的表。
DDL Script:
Create table SelectedTags
(Name nvarchar(20));
INSERT INTO SelectedTags values ('ruby'),('rails')
t - sql:
DECLARE @list nvarchar(max)
SELECT @list=coalesce(@list+',','')+st.Name FROM SelectedTags st
SELECT * FROM Tags
WHERE CharIndex(Name,@Param)>0
在我看来,正确的方法是将列表存储在字符串中(长度受DBMS支持的限制);唯一的技巧是(为了简化处理)我在字符串的开头和结尾都有一个分隔符(在我的例子中是一个逗号)。其思想是“动态规范化”,将列表转换为单列表,每个值包含一行。这让你可以转弯
In (ct1,ct2, ct3…卡通)
成一个
在(选择…)
或者(我可能更喜欢的解决方案)常规连接,如果你只是添加一个“distinct”来避免列表中重复值的问题。
不幸的是,分割字符串的技术是相当特定于产品的。 下面是SQL Server的版本:
with qry(n, names) as
(select len(list.names) - len(replace(list.names, ',', '')) - 1 as n,
substring(list.names, 2, len(list.names)) as names
from (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' names) as list
union all
select (n - 1) as n,
substring(names, 1 + charindex(',', names), len(names)) as names
from qry
where n > 1)
select n, substring(names, 1, charindex(',', names) - 1) dwarf
from qry;
Oracle版本:
select n, substr(name, 1, instr(name, ',') - 1) dwarf
from (select n,
substr(val, 1 + instr(val, ',', 1, n)) name
from (select rownum as n,
list.val
from (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' val
from dual) list
connect by level < length(list.val) -
length(replace(list.val, ',', ''))));
MySQL版本:
select pivot.n,
substring_index(substring_index(list.val, ',', 1 + pivot.n), ',', -1) from (select 1 as n
union all
select 2 as n
union all
select 3 as n
union all
select 4 as n
union all
select 5 as n
union all
select 6 as n
union all
select 7 as n
union all
select 8 as n
union all
select 9 as n
union all
select 10 as n) pivot, (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' val) as list where pivot.n < length(list.val) -
length(replace(list.val, ',', ''));
(当然,“pivot”必须返回与的最大行数相同的行数 我们可以在列表中找到的项目)
也许我们可以在这里使用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)