Using Parameters with an SQL IN Clause

[jmcilhinney]

VB version here.

Myself and others suggest with regularity that values be inserted into SQL statements using parameters rather than string concatenation. Using parameters is more secure, more readable, less error-prone and negates the need to escape single quotes in text values.

Using parameters can be a bit tricky with an IN clause though, e.g.

SELECT * FROM MyTable WHERE ID IN (3, 10, 17)

You cannot use a single parameter for the whole list, but if you don't know how many values will be in the list then you can't add the parameters easily.

The way to work around this is to use a combination of string concatenation and parameters. You build a list of parameter place holders using string concatenation, then add the corresponding parameters to the command.

As an example, assume you have a ListBox containing possible field values. If the user can select zero, one or more of the items in the list to filter the results you could do this:

SqlConnection connection = new SqlConnection("connection string here");
SqlCommand command = new SqlCommand();
StringBuilder query = new StringBuilder("SELECT * FROM MyTable");
 
switch (this.listBox1.SelectedItems.Count)
{
    case 0:
        break;
    case 1:
        query.Append(" WHERE MyColumn = @MyColumn");
        command.Parameters.AddWithValue("@MyColumn", this.listBox1.SelectedItem);
        break;
    default:
        query.Append(" WHERE MyColumn IN (");
 
        string paramName;
 
        for (int index = 0; index < this.listBox1.SelectedItems.Count; index++)
        {
            paramName = "@MyColumn" + index.ToString();
 
            if (index > 0)
            {
                query.Append(", ");
            }
 
            query.Append(paramName);
            command.Parameters.AddWithValue(paramName, this.listBox1.SelectedItems[index]);
 
        }
 
        query.Append(")");
        break;
}
 
command.CommandText = query.ToString();
command.Connection = connection;