Using CASE Expression in SQL Server to create a Computed Column – Avoiding Nulls

I had recently posted on Using CASE Expression in SQL Server to create a Computed Column. Burt C commented asking if it was possible to avoid NULLS for the rows that did not match a condition. The answer is that it is possible to avoid NULLS and can be done easily using COALESCE

Here’s the modified query:

DECLARE @tmp TABLE(Quantity integer,
Price decimal,
DiscountedPrice AS CAST (
 COALESCE(
        CASE
            WHEN Quantity BETWEEN 10 and 20 THEN Price - Price * .10
            WHEN Quantity BETWEEN 20 and 30  THEN Price - Price * .20
            WHEN Quantity >=30 THEN Price -  Price * .40
        END
  ,0)
    AS Money)
)
   
   
INSERT INTO @tmp values(4, 4000)
INSERT INTO @tmp values(16, 3500)
INSERT INTO @tmp values(12, 5000)
INSERT INTO @tmp values(22, 6620)
INSERT INTO @tmp values(5, 6400)
INSERT INTO @tmp values(21, 1000)
INSERT INTO @tmp values(37, 7000)

SELECT * FROM @tmp

OUTPUT

As you can see, all the NULLS got replaced with 0

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook