SQL WHERE clause not returning rows when field has NULL value

Posted by JohnB on Stack Overflow See other posts from Stack Overflow or by JohnB
Published on 2011-01-15T01:23:06Z Indexed on 2011/01/16 1:53 UTC
Read the original article Hit count: 571

Filed under:
|
|

Ok, so I'm aware of this issue:

When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN

However, I am trying to query a DataTable.

I could add to my query:

OR col_1 IS NULL OR col_2 IS NULL

for every column, but my table has 47 columns, and I'm building dynamic SQL (string concatenation), and it just seems like a pain to do that. Is there another solution?

I was to bring back all the rows that have NULL values in the WHERE comparison.

UPDATE

Example of query that gave me problems:

string query = col_1 not in ('Dorothy', 'Blanche') and col_2 not in ('Zborna', 'Devereaux')
grid.DataContext = dataTable.Select(query).CopyToDataTable();

(didn't retrieve rows if/when col_1 = null and/or col_2 = null)

© Stack Overflow or respective owner

Related posts about c#

Related posts about sql