IN clause in SQL with no value - C#

Question

I have following query used by c# program:

var names = string.Join(",", activeNames.Select(n => string.Format("'{0}'", n)).ToArray());

var query = @"SELECT * FROM TableA WHERE NOT Name IN (" + names + ")";

The above query works as long as there is value in names like if name = 'Ken', 'John' this works. But if name is empty query looks like this:

SELECT * FROM TableA WHERE NOT Name IN ()

which is invalid syntax how can i make this query work?

I can use two seperate queries like: SELECT * FROM TableA //if name is empty

or above query if name has values. But is this right way to approch this?

If you do the right thing and pass arrays as table valued parameters then you end up with an empty parameter. — ta.speot.is, Jan 29 '13 at 21:33

score 1 · Answer 1 · answered Jan 29 '13 at 21:29

1

If Name is never empty, you could add an empty value to the IN clause:

@"SELECT * FROM TableA WHERE NOT Name IN (''," + names + ")";

answered Jan 29 '13 at 21:29

Francis P

12,709
2
26
49

2

This only works if you don't actually have a blank name in the DB. – PinnyM Jan 29 '13 at 21:30
2

@PinnyM, I actually specified "If Name is never empty" to avoid this kind of comment... – Francis P Jan 29 '13 at 21:31

score 1 · Accepted Answer · answered Jan 29 '13 at 21:33

1

Sure that'll work.

var query = @"SELECT * FROM TableA"
if(activeNames.length > 0)
  query += " WHERE NOT Name IN (" + names + ")";

answered Jan 29 '13 at 21:33

Magnus

43,221
7
76
112

score 0 · Answer 3 · answered Jan 29 '13 at 21:29

0

You can just add validation for the value of the names variable.

string query = string.empty;

if (string.IsNullOrEmpty(names))
{
 query = "SELECT * FROM TableA";
}
else
{
 query = "SELECT * FROM TableA WHERE NOT Name IN ("....
}

answered Jan 29 '13 at 21:29

Xtian Macedo

835
5
19

score 0 · Answer 4 · edited May 23 '17 at 12:04

Just use separate queries. Based on your information, there is no need to hack the WHERE-clause.

var query = @"SELECT * FROM TableA";
if (activeNames.Any()){
    var names = string.Join(",", activeNames
                                    .Select(n => string.Format("'{0}'", n))
                                    .ToArray());

    query += " WHERE NOT Name IN (" + names + ")";
}

But please, use parameterized queries! See this question on how to do this.

IN clause in SQL with no value - C#

4 Answers4