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.[highlight=vb]SELECT * FROM MyTable WHERE ID IN (3, 10, 17)[/highlight]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:[highlight=vb]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[/highlight]

 

[stanav]

Excellent technique, JMC :thumb:

 

[wild_bill]

Here's another example if you're required to use stored procedures for data access: This was tested with visual studio 2005, and SQL server 2000
[highlight=vb]
Dim ds As New DataSet
Dim myData As New DataTable("Root")
myData.Columns.Add("Data")
myData.Rows.Add("1")
myData.Rows.Add("2")
ds.Tables.Add(myData)

Dim connection As New SqlConnection("server=servername;database=dbname;trusted_connection=true")
Dim command As New SqlCommand("spname", connection)
Dim adapter As New SqlClient.SqlDataAdapter(command)
Dim returnedData As New DataSet

command.CommandType = CommandType.StoredProcedure
command.Parameters.AddWithValue("@filterCriteria", ds.GetXml)

connection.Open()
adapter.Fill(returnedData)
connection.Close()
[/highlight]

Create  PROCEDURE spname
	@filterCriteria text
AS

DECLARE @DocHandle int

EXEC sp_xml_preparedocument @DocHandle OUTPUT, @filterCriteria

SELECT	*
FROM	mytable
WHERE	columnname in (	select data
			from openxml (@DocHandle,'/NewDataSet/Root',2)
			With (Data varchar(100)))

EXEC sp_xml_removedocument @DocHandle

 

[jmcilhinney]

Here's another variation:
[highlight=vb]
Dim connection As New SqlConnection("connection string here")
Dim command As New SqlCommand
Dim query = "SELECT * FROM MyTable"

Select Case ListBox1.SelectedItems.Count
Case 1
query &= " WHERE MyColumn = @MyColumn"
command.Parameters.AddWithValue("@MyColumn", ListBox1.Text)
Case Is > 1
Dim parameterNames = Enumerable.Range(0, ListBox1.SelectedItems.Count).
Select(Function(n) "@MyColumn" & n).
ToArray()

query &= $" WHERE MyColumn IN ({String.Join(", ", parameterNames)})"

For i = 0 To ListBox1.SelectedItems.Count - 1
command.Parameters.AddWithValue(parameterNames(i), ListBox1.GetItemText(ListBox1.SelectedItems(i)))
Next
End Select

command.CommandText = query
command.Connection = connection
[/highlight]