PARAMETERIZING AN SQL IN CLAUSE?

Ankita Pandey

Total Post:183

Points:1285
Posted by  Ankita Pandey
 1293  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. AVADHESH PATEL

    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

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!