Results 1 to 4 of 4

Thread: Using Parameters with an SQL IN Clause

  1. #1

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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:
    1. 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:
    1. Dim connection As New SqlConnection("connection string here")
    2. Dim command As New SqlCommand
    3. Dim query As New StringBuilder("SELECT * FROM MyTable")
    4.  
    5. Select Case Me.ListBox1.SelectedItems.Count
    6.     Case 1
    7.         query.Append(" WHERE MyColumn = @MyColumn")
    8.         command.Parameters.AddWithValue("@MyColumn", Me.ListBox1.SelectedItem)
    9.     Case Is > 1
    10.         query.Append(" WHERE MyColumn IN (")
    11.  
    12.         Dim paramName As String
    13.  
    14.         For index As Integer = 0 To Me.ListBox1.SelectedItems.Count - 1 Step 1
    15.             paramName = "@MyColumn" & index
    16.  
    17.             If index > 0 Then
    18.                 query.Append(", ")
    19.             End If
    20.  
    21.             query.Append(paramName)
    22.             command.Parameters.AddWithValue(paramName, Me.ListBox1.SelectedItems(index))
    23.         Next index
    24.  
    25.         query.Append(")")
    26. End Select
    27.  
    28. command.CommandText = query.ToString()
    29. command.Connection = connection

  2. #2
    PowerPoster stanav's Avatar
    Join Date
    Jul 2006
    Location
    Providence, RI - USA
    Posts
    9,289

    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 -

  3. #3
    Code Monkey wild_bill's Avatar
    Join Date
    Mar 2005
    Location
    Montana
    Posts
    2,993

    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:
    1. Dim ds As New DataSet
    2.         Dim myData As New DataTable("Root")
    3.         myData.Columns.Add("Data")
    4.         myData.Rows.Add("1")
    5.         myData.Rows.Add("2")
    6.         ds.Tables.Add(myData)
    7.  
    8.         Dim connection As New SqlConnection("server=servername;database=dbname;trusted_connection=true")
    9.         Dim command As New SqlCommand("spname", connection)
    10.         Dim adapter As New SqlClient.SqlDataAdapter(command)
    11.         Dim returnedData As New DataSet
    12.  
    13.         command.CommandType = CommandType.StoredProcedure
    14.         command.Parameters.AddWithValue("@filterCriteria", ds.GetXml)
    15.  
    16.         connection.Open()
    17.         adapter.Fill(returnedData)
    18.         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.

  4. #4

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Using Parameters with an SQL IN Clause

    Here's another variation:
    vb.net Code:
    1. Dim connection As New SqlConnection("connection string here")
    2. Dim command As New SqlCommand
    3. Dim query = "SELECT * FROM MyTable"
    4.  
    5. Select Case ListBox1.SelectedItems.Count
    6.     Case 1
    7.         query &= " WHERE MyColumn = @MyColumn"
    8.         command.Parameters.AddWithValue("@MyColumn", ListBox1.Text)
    9.     Case Is > 1
    10.         Dim parameterNames = Enumerable.Range(0, ListBox1.SelectedItems.Count).
    11.                                         Select(Function(n) "@MyColumn" & n).
    12.                                         ToArray()
    13.  
    14.         query &= $" WHERE MyColumn IN ({String.Join(", ", parameterNames)})"
    15.  
    16.         For i = 0 To ListBox1.SelectedItems.Count - 1
    17.             command.Parameters.AddWithValue(parameterNames(i), ListBox1.GetItemText(ListBox1.SelectedItems(i)))
    18.         Next
    19. End Select
    20.  
    21. command.CommandText = query
    22. 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
  •  



Click Here to Expand Forum to Full Width