Luciano Evaristo Guerche

A brazilian geek interested in .NET technologies

About Me

ISNULL + NULLIF instead of CASE WHEN

Today I had to write a SQL statement quite similar to the sample below:

SELECT ProductID,
       ProductName,
       CASE WHEN ProductDescription IS NULL OR ProductDescription = ''
       THEN '<no description>'
       ELSE ProductDescription END AS ProductDescription
FROM Products
ORDER BY ProductName

 

Then I changed it, replacing CASE WHEN by ISNULL and NULLIF, as can be seen below:

SELECT ProductID,
       ProductName,
       ISNULL(NULLIF(ProductDescription, ''), '<no description>') AS ProductDescription
FROM Products
ORDER BY ProductName

 

What do you think about this kind of construction. Drop me a line and let me know what your feelings about

Posted: Jun 08 2005, 06:02 PM by Luciano Evaristo Guerche | with 5 comment(s)
Filed under: , , , ,

Comments

Jerry Pisk said:

I think this points to an oversight in the database design - no description should be designed by NULL not by an empty string. Otherwise I think supplying UI elements should be done in the presentation layer, not in the database. Simply return an empty string (or NULL) and have the UI handle the presentation of a missing description value. How are you going to localize it in the database?
# June 8, 2005 4:25 PM

Alex Papadimoulis said:


If you prefer your code to be harder to read, then go with your ISNULL+NULLIF technique. If you think that the programmer who follows you would appreciate something understandable, you should probably use the standard CASE.

That, and what Jerry Pisk said.
# June 8, 2005 5:00 PM

Luciano Evaristo Guerche said:

Jerry and Alex,

Thanks a bunch for the feedback. Actually, in the real situation, I had to dealt with a field whose type was float and the CASE WHEN or ISNULL+NULLIF was used as a workaround to a bad database design defined by another professional long time ago.
# June 9, 2005 10:01 AM

Jerry Pisk said:

You can also use COALESCE(NULLIF(<column>, <null value>), <replacement value>) which may be just a tad easier to understand.
# June 9, 2005 4:30 PM

Luciano Evaristo Gueche said:

Jerry,

Since you mentioned COALESCE, I would add it allows more than two paramaters so that instead of ISNULL(ISNULL(ISNULL(x, y), z), t) it could be COALESCE(x, y, z, t). I often use COALESCE when I have to deal with more than two values. Thanks for the tip and feedback.
# June 12, 2005 8:47 AM
Leave a Comment

(required) 

(required) 

(optional)

(required)