Parameterizing an SQL IN clause?

Total Post:183


 1714  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!

  1. 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.


Please check, If you want to make this post sponsored

You are not a Sponsored Member. Click Here to Subscribe the Membership.