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

Computer Columns COALESCE

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

2 comments:

tripy said...

Or you could use another statement in your switch...

DECLARE @tmp TABLE(Quantity integer,
Price decimal,
DiscountedPrice AS CAST (
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
WHEN Quantity IS NULL THEN 0
END
AS Money)
)

Laquedas said...

Or you can use the isNull function:

DECLARE @tmp TABLE(Quantity integer,
Price decimal,
DiscountedPrice AS CAST (
isnull(
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