How to correctly pass an array as a parameter to a SQL query?

Question

SQL

We have such a request.

SQLname = "SELECT name FROM dbo.table WHERE id IN("+String.Join(",", items.ToArray())+");";
                using (var comm = new SqlCommand(SQLname, conn, null))
                {

                    using (var reader = comm.ExecuteReader())
                    {
                        while (reader.Read())
                        {
   
                        }                          
                    }
                }

What is the best way to pass a list for IN?
Maybe somehow you can parameterize or do it more correctly, the main problem is that the values ​​\u200b\u200bcan be from 1 to 1000.

Answer 1

Not the newest article, but nevertheless: Tests of methods for passing list variables to storage... .

Answer 2

I think through xml the parameter will be the easiest way. In procedure you will make join.
something like:

create procedure p(
  @ids xml
)
begin
  select name 
  from 
    dbo.table 
    inner join @ids.nodes('/items/item') root(items) 
      on id = items.value('(text())[1]', 'int')
end

in the example xml will look like

<items>
  <item>1</item>
  ...
  <item>1000</item>
</items>