Update query's where clause values from a datatable

Question

I have this query:

UPDATE A
Set A.NUM = B.NUM
FROM A
JOIN B  on A.ID = B.ID
where A.Code in ()

A.Code values are from a datatable. How do I feed into this query?

Answer 1

You want a Table-Valued Parameter.

This article will also help:

http://www.brentozar.com/archive/2014/02/using-sql-servers-table-valued-parameters/

If you have more columns than just Code in the C# Datatable, you may also need a projection (inside the sql) get output that will work with the IN() clause. Something like this:

UPDATE A
Set A.NUM = B.NUM
FROM A
JOIN B  on A.ID = B.ID
where A.Code in ( SELECT Code FROM @tvpCodes )

Answer 2

Why not write a code to make a comma separated string of ID's using datatable?

            string lstOfIDs = string.Empty;
            DataTable dt = new DataTable();
            foreach (DataRow drow in dt.Rows)
            {
                lstOfIDs += drow["IdColumnHere"].ToString()+",";
            }

            lstOfIDs.TrimEnd(',');

You can then pass the lstOfIds in the IN clause.

EDIT 1:

I think A.Code In () is checking for code not Ids. I hope you are placing codes in the lstOfIDs. Also, I would advise putting ' between Id's. i.e.

lstOfIDs += "'"+drow["IdColumnHere"].ToString()+"',";

this should give you something like 'abc','def','anything'