Parameterize an SQL IN clause

Question

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 elegant way specific to SQL Server 2008, I am open to that.

Answer 1

You can use this technique given below:

SELECT * FROM Tags

WHERE '|ruby|scruffy|rails|rubyonrails|'

LIKE '%|' + name + '|%'

Want to learn SQL from basics! Here's the right video for you on SQL provided by Intellipaat:

 

So, here's the C# code you can refer to:

 string[] tags = new string[] {"rails", "ruby", "scruffy", "rubyonrails" };

const string cmdText = "select * from tags where '|' + @tags1 + '|' like '%|' + name + '|%'";

using (SqlCommand cmd1 = new SqlCommand(cmdText)) {

cmd1.Parameters.AddWithValue("@tags", string.Join("|", tags1);

}

 Disadvantages are as follows:

 LIKE "%...%" queries are not indexed. So, the performance is poor. Make sure you don't have any blank, | or null tags otherwise it won't work.