-
Jan 5th, 2008, 06:55 AM
#1
Using Parameters with an SQL IN Clause
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.
SQL Code:
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:
VB.NET Code:
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
Last edited by jmcilhinney; Oct 28th, 2008 at 09:35 PM.
-
Jun 23rd, 2008, 08:47 PM
#2
Re: Using Parameters with an SQL IN Clause
Excellent technique, JMC
Let us have faith that right makes might, and in that faith, let us, to the end, dare to do our duty as we understand it.
- Abraham Lincoln -
-
Jul 16th, 2008, 01:50 PM
#3
Re: Using Parameters with an SQL IN Clause
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
vb Code:
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()
Code:
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
Last edited by wild_bill; Jul 17th, 2008 at 12:01 PM.
-
Mar 31st, 2020, 11:39 PM
#4
Re: Using Parameters with an SQL IN Clause
Here's another variation:
vb.net Code:
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|