Home > DeveloperSection > Forums > Parameterizing an SQL IN clause?
Chintoo Semi

Total Post:135

Points:947
Posted on    April-17-2013 1:34 AM

 MSSQL Server MSSQL Server 
Ratings:


 1 Reply(s)
 1491  View(s)
Rate this:
Hi All!

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

Thanks in advance! 



AVADHESH PATEL

Total Post:604

Points:4228
Posted on    April-17-2013 9:01 AM

Hi Chintoo!

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);
}

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

Follow MindStick