SQL Server query using Parameters for an IN Clause

Question

I am trying to update using the IN clause, however I can't seem to get it to work. My iArr creates a list of numbers separated with a comma and in theory once enclosed in brackets it should update however it doesn't appear to work. I keep getting an error

Conversion failed when converting nvarchar value '111111, 222222, 333333' to datatype int.

Any idea how to get round this? Full code below:

    Dim iArr As String
    iArr = ""
    For i As Integer = 0 To ufReview.InvoiceGrid.Rows.Count - 1
        iArr = iArr & ufReview.InvoiceGrid.Rows(i).Cells(0).Value & ", "
    Next i
    iArr = Left(iArr, Len(iArr) - 2)

    Dim SQL_Arr As String = _
            <SQL>
               UPDATE SCInv SET Inv_Transfer_Date = @UpdateCommand WHERE Inv_Num IN (@Array)
            </SQL>

    Dim cnt As New SqlConnection()
    Dim cmd As New SqlCommand()
    cnt.ConnectionString = "Data Source=SERVERNAME;Initial Catalog=CAT;User ID=USERID;Password=PASSWORD"
    Try
        cnt.Open()
        cmd.Connection = cnt
        cmd.CommandText = SQL_Arr
        cmd.Parameters.Clear()
        cmd.Parameters.AddWithValue("@UpdateCommand", UpdateCommand)
        cmd.Parameters.AddWithValue("@Array", iArr)
        Dim RACount As Integer = cmd.ExecuteNonQuery()
        MsgBox("The following Summary Numbers are now marked as " & UpdateFeedback & ": " & vbCrLf & iArr _
               & vbCrLf & vbCrLf & "(" & RACount & " row(s) affected)", vbOKOnly + vbInformation, "Tesseract")
    Catch ex As Exception
        MsgBox("Failed to update, please try again" & vbCrLf & vbCrLf & ex.Message, vbOKOnly + vbCritical, "SQL Error")
    End Try
    cnt.Close()
    cnt.Dispose()

Answer 1

It is possible that SQL Server now supports passing arrays for parameters. Many DBs do not, and there is still an open request for this on Microsoft Connect. It would be worth finding out.

You should also turn on Option Strict. Your SQL_Arr is declared as string, but you assign an XML literal to it, then pass that literal as the command text.

This will create a SQL statement where each item in the Id List is given its own parameter and then the value it set for it. It uses some random data for the Ids to emulate a varying list and it uses MySQL...the concepts will be the same.

' test data: random set of IDs to update
Dim idList = Enumerable.Range(1, 2500).OrderBy(Function(r) RNG.Next()).Take(8).ToList()

Dim sql As String = <sql>
            UPDATE SampleX SET `Value`= @v WHERE Id IN (@theList)
          </sql>.Value

' create the parameter placeholders
' results in a list of "@pX" items where X is a number
Dim params = Enumerable.Range(1, idList.Count).
            Select(Function(q) String.Format("@q{0}", q)).
            ToList()

' join the list of parms to make '@q1, @q2...'
' then replace @thelist with that string
sql = sql.Replace("@theList", String.Join(",", params))

' run the query
Using dbcon As New MySqlConnection(MySQLConnStr)
    Using cmd As New MySqlCommand(sql, dbcon)

        ' specify the value
        cmd.Parameters.Add("@v", MySqlDbType.Int32).Value = -6

        ' loop to supply an Id value for each IN parameter
        ' placeholder created
        For n As Int32 = 0 To params.Count - 1
            cmd.Parameters.Add(params(n), MySqlDbType.Int32).Value = idList(n)
        Next

        dbcon.Open()
        Dim rows = cmd.ExecuteNonQuery()
    End Using

    ' debug/demo: show the results
    sql = "SELECT Name, Descr, Value, Country FROM SampleX WHERE Value=-6"
    Using cmd As New MySqlCommand(sql, dbcon)
        dtSample = New DataTable
        dtSample.Load(cmd.ExecuteReader())
        dgv1.DataSource = dtSample
    End Using
End Using

We want the query to remain parameterized, so the first part creates a list of @q1 placeholders - as many of them as there are Ids to update. To create the placeholders in a loop:

Dim params As New List(Of String)
For n As Int32 = 0 To idList.Count - 1
    params.Add(String.Format("@q{0}", (n + 1).ToString()))
Next

Next, the code replaces @theList in the SQL with the joined version of the list. The result:

UPDATE SampleX SET `Value`= @v WHERE Id IN (@q1,@q2,@q3,@q4,@q5,@q6,@q7,@q8)

If the IN clause was dealing with strings rather than integers, add ticks to the parameters creation: String.Format("'@q{0}'",...

The rest is straight forward: specify the Id for each parameter:

' params(n) e.g. "q1"  and idList(n) is the value e.g. 11111
cmd.Parameters.Add(params(n), MySqlDbType.Int32).Value = idList(n)

My sample just set the a value to -6 to make them easy to find. It works:

Finally, note that there is a maximum length allowed for the resulting SQL. That max depends on the DB Provider and is pretty large, but if there are a great many Ids for your IN clause, you may want to break it up into batches.