passing an Array as a Parameter to be used in a SQL Query using the "IN" Command

Question

Good Afternoon to All,

I have a question concerning on SQL Queries. is it possible to use an array as a parameter to a query using the "IN" command?

for example,

int x = {2,3,4,5}

UPDATE 'table_name' set 'field' = data WHERE field_ID IN (x)

the reason I am asking this is to avoid an iterative SQL Statement when I have to update data in a database. I also thought of using a for each statement in for the UPDATE Query but I don't know if it will affect the performance of the query if it will slow down the system if ever 100+ records are updated.

I am using VB.Net btw. My Database is MySQL Workbench.

Answer 1

If you have the query in a variable (not a stored procedure) and you don't have a huge amount of ids, you could built your own IN. I haven't tested the speed of this approach.

This code won't compile, it's just to give you an idea.

query = "SELECT * FROM table WHERE col IN ("

For t = 0 TO x.Length-1
    If t > 0 Then query &= ","

    query &= "@var" & t
Next

query &= ")"

...

For t = 0 TO x.Length-1
    cmd.Parameters.Add("@var" & t, SqlDbType.Int).Value = x(t)
Next