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

5 Comments

  • 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?



  • 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.

  • 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.

  • You can also use COALESCE(NULLIF(&lt;column&gt;, &lt;null value&gt;), &lt;replacement value&gt;) which may be just a tad easier to understand.

  • 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.

Comments have been disabled for this content.