Parameterized Queries with LIKE and IN conditions

Let's say that you have your category ids in an integer array and Name is a string. The trick is to create the command text to allow you to enter all of your category ids as individual parameters and construct the fuzzy match for name. To do the former, we use a loop to construct a sequence of parameter names @p0 through @pN-1 where N is the number of category ids in the array. Then we construct a parameter and add it to the command with the associated category id as the value for each named parameter. Then we use concatenation on the name in the query itself to allow the fuzzy search on name.

string Name = "someone";int[] categoryIDs = new int[] { 238, 1138, 1615, 1616, 1617,                                1618, 1619, 1620, 1951, 1952,                                1953, 1954, 1955, 1972, 2022 };SqlCommand comm = conn.CreateCommand();string[] parameters = new string[categoryIDs.Length];for(int i=0;i<categoryIDs.Length;i++){   parameters[i] = "@p"+i;   comm.Parameters.AddWithValue(parameters[i], categoryIDs[i]);}comm.Parameters.AddWithValue("@name",$"%{Name}%");comm.CommandText = "SELECT * FROM Products WHERE Category_ID IN (";comm.CommandText += string.Join(",", parameters) + ")";comm.CommandText += " OR name LIKE @name";

This is a fully parameterized query that should make your DBA happy. I suspect that since these are integers, though it would not be much of a security risk just to construct the command text directly with the values, while still parameterizing the name. If your category ids are in a string array, just split the array on commas, convert each to an integer, and store it in the integer array.

Note: I say array and use it in the example, but it should work for any collection, although your iteration will probably differ.

Original idea from http://www.tek-tips.com/viewthread.cfm?qid=1502614&page=9

You need "%" in value of sql parameter.

SqlCommand comm = new SqlCommand("SELECT * FROM Products WHERE Category_ID IN (@categoryid1, @categoryid2) OR name LIKE @name", conn);comm.Parameters.Add("@categoryid1", SqlDbType.Int);comm.Parameters["@categoryid1"].Value = CategoryID[0];comm.Parameters.Add("@categoryid2", SqlDbType.Int);comm.Parameters["@categoryid2"].Value = CategoryID[1];comm.Parameters.Add("@name", SqlDbType.NVarChar);comm.Parameters["@name"].Value = "%" + Name + "%";

This approach will not work. Period.

The IN clause expects a list of parameters itself, so when you bind one parameter to it, you have the chance to pass in one value.

Build your statement string dynamically, with the exact amount of individual IN clause placeholders you intend to pass in, and then add parameters and bind values to them in a loop.