参数化SQL IN子句 [英] Parameterize an SQL IN clause
本文介绍了参数化SQL IN子句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何对包含IN
子句的查询进行参数化,像这样的子句?
How do I parameterize a query containing an IN
clause with a variable number of arguments, like this one?
SELECT * FROM Tags
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC
在此查询中,参数的数量可以为1到5之间的任何地方.
In this query, the number of arguments could be anywhere from 1 to 5.
我不希望对此(或XML)使用专用的存储过程,但是如果 SQL Server 2008 ,我对此持开放态度.
I would prefer not to use a dedicated stored procedure for this (or XML), but if there is some elegant way specific to SQL Server 2008, I am open to that.
推荐答案
这是我使用的一种快速处理技术:
Here's a quick-and-dirty technique I have used:
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);
}
两个警告:
- 表现糟透了.
LIKE "%...%"
查询未建立索引. - 确保您没有任何
|
,空白或null标记,否则将无法正常工作
- The performance is terrible.
LIKE "%...%"
queries are not indexed. - Make sure you don't have any
|
, blank, or null tags or this won't work
还有其他一些方法可以使某些人认为它更清洁,因此请继续阅读.
There are other ways to accomplish this that some people may consider cleaner, so please keep reading.
这篇关于参数化SQL IN子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文