NullDisplayText in markup vs ISNULL(field, 0) in SQL?

Question

Which approach is better to use:

• BoundField.NullDisplayText isn't set. NULL-case is foreseen in SQL query, i.e. SELECT ISNULL(amount, 0) FROM table

or

• BoundField.NullDisplayText is set, e.g. "0.00 %". NULL-case isn't foreseen in SQL query, i.e. SELECT amount FROM table

What do you think?

Answer 1

Clearly the first one, because you filter with ISNULL.

Answer 2

The difference is that 0.00 implies that the field has a value, whereas NULL implies that the opposite. From a data sanity perspective, therefore, the former is correct.

Answer 3

I would argue that the second choice is better. It is generally better to format output in the middle-tier or presentation tier rather than the database. Thus, I would want to return nulls to the tier above the data tier code and have it decide what to do about display rather than make the choice at the database.

By converting nulls to zeros, you are stating for all systems that use the query that a null equates to a user intentionally entering a zero. If that is actually the case, then fine, use Coalesce instead of IsNull and convert nulls to zeroes. However, if there is even the remotest possibility that the query will be reused and that the absence of a value might be treated differently than the entry of a zero, I would return nulls to the middle tier and let it decide what to do about it.

Answer 4

Can you do best of both?

• ISNULL only serves this single case
• Formatting/logic should be in the UI
• Other clients using "amount" may expect NULL so you now have an inconsistent "contract"
• 0 means zero, NULL means unknown/nothing: 2 different states