Using parameters to build sql statement IN?

Question

I'm trying to find a proper way to do this instead of simple string concatenation.

I have a (simplified) SQL statement as follows:

SELECT * FROM Zones WHERE id in (@zoneIDs);

I plan to insert an @zoneIDs value like such: 100000,100001,100002

I'm using VB in this particular instance, but it should apply to any .Net language using the MySQL connector. What I'm trying is this:

Private GetZones As String = "SELECT * FROM Zones WHERE id in (@zoneIDs)"
Dim Param As MySqlParameter = New MySqlParameter("@zoneIDs", MySqlDbType.String)
Param.Value = "100000,100001,100002"
Dim adapter As MySqlDataAdapter = New MySqlDataAdapter(GetZones)
Dim dt as DataTable = New DataTable()
adapter.Fill(dt)

But what's getting filled in dt is just the row for ID 100000, as if none of the others were in the query. My guess is that it's running a command like this:

SELECT * FROM Zones WHERE id in ("100000,100001,100002");

Instead of

SELECT * FROM Zones WHERE id in (100000,100001,100002);

I personally prefer to use Stored Procedures instead of strings like this, but I believe I would hit a similar wall there. What is the proper way to fill an IN clause using parameters like this?

Joel Spolsky's answer is a performance joke. I'd rather go with Mark Brackett's answer, but that is still sort of a duct taping solution. I'm hoping that there's some way to do this properly. If this were a stored procedure, I would not be able to do either solution. — Corey Ogburn, Sep 19 '12 at 17:03
It would be nice if there were an elegant solution to this notorious problem. You could solve it yourself with some .net extension classes. — O. Jones, Sep 19 '12 at 17:36
Have you tried putting the different zoneID's into a string array, and iterating through the array and for each iteration, add the returned row to a declared dataset? Albeit more intensive on the server (depending on how many rows you could return), it works. If further explanation is needed, let me know. — Josh McKearin, Sep 19 '12 at 17:49

Josh McKearin · Answer 1 · 2012-09-19T18:44:52.280

To further explain my comment, you can use a function for your SQL query similar to the one you already have:

Private Function GetZones(ByVal zoneID As String) As DataRow
    Dim connStr As String = "blah" 'Try getting this from app.config'
    Dim conn As New MySqlConnection(connStr)
    Dim cmd As New MySqlCommand()
    Dim dt As DataTable = Nothing
    Dim sSQL As String = "SELECT * FROM Zones WHERE id in (@zoneID)"
    Dim da As New MySqlDataAdapter(sSQL, conn)

    With cmd
        .CommandType = CommandType.Text
        .CommandText = sSQL
        .Connection = conn
        .Parameters.AddWithValue("@zoneID", zoneID)
    End With
    Try
        conn.Open()
        da.Fill(dt)
    Catch ex As Exception
      'Blah'
    Finally
       conn.Close()
    End Try
    Return dt.Rows(0)
End Function

Then, in your code, you can do something such as this:

Public Function GetZonesTable(ByVal zoneIDs() As String) As DataTable
    Dim zonesDT As DataTable = Nothing

    For Each zoneID As String In zoneIDs
        zonesDT.Rows.Add(GetZones(zoneID))
    Next

    Return zonesDT
End Function

What you are doing is building your array of zoneIDs and passing that array to the GetZonesTable function, which then creates a DataTable in memory, calls the GetZones() function and passes each zoneID in the array to it. For each zoneID in the zoneIDs array, it then adds the returned row to the zonesDT and the GetZonesTable returns your final table. Although this isn't too harsh on the server as-is, it can become fairly intensive depending on the query itself and the number of zoneIDs you have stored in the array.

There are some SQL tricks depending on your version of SQL that may be less intensive, but they are much more in-depth than my explanation above. If that is needed, then let me know what version of SQL you are using.

This hits the database once for each zone and is very limiting. — Corey Ogburn, Sep 20 '12 at 16:21
I clearly stated above that this would query the database for each zoneID. As far as it being "limiting", can you elaborate on that and give an alternative to my answer which doesn't query the database for each zoneID for those that will see this in the future. — Josh McKearin, Sep 20 '12 at 21:15
It becomes a huge challenge to sort, group, or join across all the IDs because they're now divided into different requests. — Corey Ogburn, Sep 21 '12 at 14:50
Try making your array of Integer datatype instead of String. — Josh McKearin, Sep 22 '12 at 06:00

Using parameters to build sql statement IN?

1 Answers1