Parameterizing an SQL IN clause?

Total Post:183

Points:1285
 1517  View(s)
Ratings:
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!

  1. Post:604

    Points:4228
    Re: Parameterizing an SQL IN clause?

    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.

Answer