Home > DeveloperSection > Forums > Parameterizing an SQL IN clause?
Ankita Pandey
Ankita Pandey

Total Post:183

Points:1285
Posted on    May-06-2013 1:56 AM

 MSSQL Server MSSQL Server 
Ratings:


 1 Reply(s)
 912  View(s)
Rate this:
Hi Expert!

How do I parametric 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 fancy SQL Server 2008 specific way of doing it elegantly, I am open to that.

Please help me!

Thanks in advance!



AVADHESH PATEL

Total Post:604

Points:4228
Posted on    May-06-2013 9:01 AM

Hi Ankita!

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.


Don't want to miss updates? Please click the below button!

Follow MindStick