Parameterize an SQL IN clause
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
In this query, the number of arguments could be anywhere from 1 to 5.
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.
answer :
Here's a quick-and-dirty technique I have used:
select * from Tags
where '|ruby|rails|scruffy|rubyonrails|'
like '%|' + Name + '|%'
So here's the C# code:
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);
}
Two caveats:
- 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.
http://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause
