Best practice for counting values that contain NULL values

I am wondering if the best way to count a li开发者_JAVA百科st of values that contains NULL values is by doing the following?

COUNT(ISNLL(Date),'')

I ask because I have a while loop that only runs if the count > 0. IT looks like this is working but but is it the proper way? Here is my code:

WHILE SELECT(Count(*)
             FROM 
              (SELECT 1 AS a
                   FROM dbo.tblrecords r
                   LEFT JOIN dbo.tblError e
                GROUP BY r.RecordId, r.MissingCount
                HAVING r.MissingCount > COUNT(DISTINCT(ISNULL(e.[ErrorDate],''))) > 0

If the e.ErrorDate is NULL I do not want it to count this.

just add WHERE e.ErrorDate is not null to your query

Aggregate functions (COUNT, SUM, ETC) will eliminate nulls so if you want them to be counted, you can either COUNT(*) which will count records, not individual values, or do as you're doing ISNULL/COALESCE to replace null values.

If you want to count only null values, do a count(*) where the value IS NOT NULL as @Derek Kromm suggested.

FWIW, I don't think the DISTINCT is necessary as it may throw off your count if you have multiple nulls that you wish to count as more than one.

WHILE (SELECT COUNT(*) FROM sometable WHERE somefield IS NULL) > 0