How to input an array to IN clause in SQL?

Question

string[] questions= new[] { "2","5","1","11","3","1"};

    select description,count(description) 
      from descriptions 
inner join questions 
        on descriptions.qid=questions.id 
     where descriptions.question=N'hardtest' 
       and qid in (?) 
  group by description

I want to use an array in this code as an input to IN clause. How can I do that?

qid in ('" + list + "')

Possible duplicate of [Passing a SQL parameter to an IN() clause using typed datasets in .NET](https://stackoverflow.com/questions/5401641/passing-a-sql-parameter-to-an-in-clause-using-typed-datasets-in-net) — Sunil, Jan 08 '18 at 07:01
can i use list instead of array? i tried but not works List list = new List(questions); — Ali Mousavi, Jan 08 '18 at 07:05
You need to pass your items as a DataTable and use a table valued parameter. — CSharpie, Jan 08 '18 at 07:06
Better is to use the table variable if you are using MS SQL otherwise try to convert the array value to compatible with In clause. — Gulam Husain Ansari, Jan 08 '18 at 07:17

score 2 · Accepted Answer · answered Jan 08 '18 at 07:35

2

string[] questions = new[] { "2", "5", "1", "11", "3", "1" };

string qids = string.Empty;
questions.All((x) => { qids += "','" + x; return true; });
qids = qids.Substring(2);

string sql = @"
select description, count(description)
  from descriptions
inner join questions
    on descriptions.qid = questions.id
where descriptions.question = N'hardtest'
   and qid in ("+qids+@"')
group by description";

answered Jan 08 '18 at 07:35

wangguanguo

181
4

i'll wait until another optimized code ,if... – Ali Mousavi Jan 08 '18 at 07:44
This is vulnerable to SQL Injection attack. – Sunil Jan 08 '18 at 08:40
since the qid is int,the simpliest way is :questions.All((x) => { qids += Int32.TryParse(x,out id)?"','" + x:""; return true; }); – wangguanguo Jan 08 '18 at 08:57

How to input an array to IN clause in SQL?

1 Answers1