Using Parameters with an SQL IN Clause

[jmcilhinney]

C# 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:

Dim connection As New SqlConnection("connection string here")
Dim command As New SqlCommand
Dim query As New StringBuilder("SELECT * FROM MyTable")
 
Select Case Me.ListBox1.SelectedItems.Count
    Case 1
        query.Append(" WHERE MyColumn = @MyColumn")
        command.Parameters.AddWithValue("@MyColumn", Me.ListBox1.SelectedItem)
    Case Is > 1
        query.Append(" WHERE MyColumn IN (")
 
        Dim paramName As String
 
        For index As Integer = 0 To Me.ListBox1.SelectedItems.Count - 1 Step 1
            paramName = "@MyColumn" & index
 
            If index > 0 Then
                query.Append(", ")
            End If
 
            query.Append(paramName)
            command.Parameters.AddWithValue(paramName, Me.ListBox1.SelectedItems(index))
        Next index
 
        query.Append(")")
End Select
 
command.CommandText = query.ToString()
command.Connection = connection