本文介绍了参数化 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 "%...%"查询未编入索引. - 确保您没有任何
|、空白或空标签，否则这将不起作用
- 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 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！
本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益，请联系我们，我们会在确认后第一时间进行删除！