Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

At what level do you prefer to handle Null values in your application?

My personal preference at the moment is the DB. When I build a view, I ensure that I return a value in place of a NULL value. So if your front-end app were to connect to the application it would never have to handle nulls.

In my VB6 apps, I made liberal use of the IsNull function to handle Nulls.

Share your experiences.

share|improve this question

migrated from stackoverflow.com Jan 13 '11 at 16:04

This question came from our site for professional and enthusiast programmers.

1  
What did you replace Null date fields with? –  JeffO Jan 13 '11 at 22:02
    
Which application? And which use in the application? I don't see that this is really answerable. –  David Thornley Jan 13 '11 at 22:41
    
If it was reporting, the DATES were converted to strings and NULLS were replaced with empty strings. –  abhi Nov 9 '11 at 18:01

8 Answers 8

This is one of the problems with nulls. Whenever the database developer puts them into the database the application developer has to take them out again. Very often the best policy is to avoid or minimise the use of nulls in your base tables to start with. If nulls are used then it's a good idea to record somewhere exactly what the null is supposed to mean and why it is used. You can then put logic in a view or stored procedure to eliminate or replace the nulls as appropriate.

share|improve this answer
    
+1 and if your RDBMS supports extended properties, that would be a good place to record the justification and meaning of nullable columns –  Larry Coleman Jan 13 '11 at 16:19

While there's definitely no one-size-fits all answer to this question, I think you're better off leaving the data transparent. If you're handling the nulls in the database and never returning them to the application, then what is the point of storing null values at all? This seems analogous to always returning the value of an integer column + 1.

This is not to say that null should be used liberally throughout the application. If your select is simply doing IsNull(FieldName, ''), why not simply make the column non-nullable and store the empty string instead? You'll make your data more closely match your business logic and may well make your selects faster (certainly if you're filtering on that column).

share|improve this answer
    
In this specific case, I happen to be reading data to write to files. If I assign NULL values to certain variables in my front-end screen, they create exceptions. Except for the primary key, all the columns in the database allow NULL values. –  abhi Jan 13 '11 at 18:16

I used to do like what you describe, use ISNULL(Column1, '') or similar in the view or stored procedure so that I never (or rarely) had to deal with a null in the application. But I found that sometimes a NULL is just the most appropriate way to represent an "unknown" state, even inside the application. So unless I have a specific reason for the "ISNULL" type of logic, I now pass those NULLs down the pipe. In fact, I've tried to get all of my database logic as absolutely pure as possible (simple SELECT, UPDATE, INSERT, or DELETE) statements with as little logic as possible. So, I got over my fear of NULLs. Your mileage may vary!

share|improve this answer

I've once seen a list (can't find the referece, alas!) of 13 different possible semantics for NULL, varying over meanings as Unknown value, Conflicting values, Not applicable, Empty, Default.

If you try to process all of those meanings in the database, you end up with a great deal of business logic down there. Conversely, for some meanings, it can be useful to isolate the business logic layer from database internals by e.g. providing a default value. It really depends on what problem you are addressing, and what this particular kind of NULL value is intended to mean.

share|improve this answer

The problem with NULL is that it is not very descriptive. For example, is the data missing but applicable or missing and inapplicable? You may be better off, from an application perspective in having the DB (via a stored proc, for example) tell you why data is not available, so that your application can take the appropriate action.

share|improve this answer

I find it really depends on business logic. Passing NULL to a UI field that will simply display as blank is usually OK. Sometimes NULLs have to be handled in the database at the constraint level for fields where NULL cannot be allowed. Sometimes it might be handled in the middle, if the database is accessed by multiple apps - some that are OK with NULL and others that are not.

share|improve this answer

If you are asking this with regard on how to handle null within an application as a value which is unacceptable; the answer is every place where null could pose a problem.

Defensive programming is often overlooked and assumptions are made by developers that layer N will deal with validation of data and ignore the validation of the data. Unless a forced contract is in place or isolation of the code allows for flexibility in the approach you should always protect yourself from null values and/or incorrect data.

We all know what happens when you assume...

share|improve this answer

NULLs have their place. Say you have the column [FoodExpiresOn] DATETIME NULL in dbo.[FoodProducts] table. A NULL value (a twinkie never goes bad) is just as significant as a non-null value.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.