Comma separated string value not working with In Clause of sql query when executing it with ado.net

Question

I am trying to get comma separated skills Ids but getting null when executing query with ado.net

Query works fine when I run it in SQL Server Management Studio, but when I tried to execute it with ado.net, I'm getting no output.

I have 2 tables Employee and Skills with following columns :

Employee: Id, Name, Location
Skills: Id, Skills

Employee data:

Id    Name   Location
7     Abc     London
8     Xyz     London
9     Xyz     USA
10    Abc     USA

Skills:

Id   skills     EmployeeId
12    AAA Xyz     7
13    BBB         7
14    CCC         7
15    AAA         8
16    BBB         8
17    CCC         8
18    AAA         9
19    BBB         9
20    CCC         9
21    AAA         10
22    BBB         10
20    CCC         10

My code so far:

string Name = "Abc";
string Location = "London";

string csvSkillsIds = string.Join(",", Skill1, Skill2);  //AAA Xyz,BBB

'AAA Xyz','BBB' //Added single quotes to comma separated values
string skillList = string.Join(",", csvSkillsIds.Split(',').Select(x => string.Format("'{0}'", x)).ToList());

using (SqlConnection connection = new SqlConnection(""))
{
    string query = "Select  Stuff(" +
                         " (" +
                         " Select  ',' + Convert(Varchar, CD.Id)" +
                         " From   Employee  as  E" +
                         " Skills S On S.EmployeeId = E.Id " +
                         " Where  E.Name = @name " +
                         " And  E.Location = @location " +
                         " And S.Skills In (@skills)" +
                         " For Xml Path ('')" +
                         " ), 1, 1, ''" +
                         " ) As SkillId";             

    using (SqlCommand cmd = new SqlCommand(query, connection))
    {
        connection.Open();

        cmd.Parameters.AddWithValue("@name", Name);
        cmd.Parameters.AddWithValue("@location", Location);
        cmd.Parameters.AddWithValue("@skills", skillList);

        var data = cmd.ExecuteScalar();  //getting no output not even null
        connection.Close();

        if (data != null)
            return data.ToString();
        else
            return null;
    }
}

This is what I am getting in data:

But when I run below query then I am successfully getting output like: 12, 13

Select  Stuff
(
    (
        Select  ',' + Convert(Varchar, S.Id)
        From    Employee    E
        Join    Skills      S   On  S.EmployeeId = E.Id
        Where   E.Name = @name
        And     E.Location = @location
        And     S.Skills In ('AAA Xyz', 'BBB')
        For Xml Path ('')
    ), 1, 1, ''
) As Skills

So I guess problem is in below line:

 And S.Skills In (@skills)

Expected output: 12,13

`@skills` is **not** a comma separated list of values. It's a single value containing a comma separated string. This is a *very* common mistake. Since you are using c# and sql server, you better send a table valued parameter instead. — Zohar Peled, Jun 27 '16 at 06:58
[read this](http://stackoverflow.com/questions/30817019/how-to-parse-a-varchar-passed-to-a-stored-procedure-in-sql-server/30817115#30817115), [that](http://stackoverflow.com/questions/30797025/adding-a-where-clause-to-sql-query-using-delimiters/30798015#30798015), and [here](http://stackoverflow.com/questions/31965233/adding-multiple-parameterized-variables-to-a-database-in-c-sharp/31965525#31965525). — Zohar Peled, Jun 27 '16 at 07:04
@ZoharPeled:But i am not using stored procedure and the reference you have given are all using stored procedure — I Love Stackoverflow, Jun 27 '16 at 07:07
well, if you don't want to convert your query to a stored procedure then [read this question and it's answers.](http://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause/928523#928523) — Zohar Peled, Jun 27 '16 at 07:14

score 1 · Answer 1 · edited May 23 '17 at 12:33

1

You could create a SQL Server function that will split a comma-separated string as described in this answer.

Then you can use it in your query, e.g. something like:

...
" And S.Skills In (SELECT Value FROM F_Split(@skills, ','))" +
...

Your @Skills parameter should contain the comma-separated list of IDs without the single quotes:

cmd.Parameters.AddWithValue("@skills", csvSkillsIds);

edited May 23 '17 at 12:33

Community

1
1

answered Sep 27 '16 at 11:37

Joe

122,218
32
205
338

Comma separated string value not working with In Clause of sql query when executing it with ado.net

1 Answers1