tag:blogger.com,1999:blog-495177919198719500.post7656450086401481970..comments2023-12-27T20:52:29.483-08:00Comments on Microsoft Sql Server Tutorials: Using CASE Expression in SQL Server to create a Computed Column – Avoiding NullsSuprotim Agarwalhttp://www.blogger.com/profile/08349831623922214390noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-495177919198719500.post-77970059442022914082010-04-12T06:17:55.453-07:002010-04-12T06:17:55.453-07:00Or you can use the isNull function:
DECLARE @tmp ...Or you can use the isNull function:<br /><br />DECLARE @tmp TABLE(Quantity integer,<br />Price decimal,<br />DiscountedPrice AS CAST (<br /> isnull(<br /> CASE<br /> WHEN Quantity BETWEEN 10 and 20 THEN Price - Price * .10<br /> WHEN Quantity BETWEEN 20 and 30 THEN Price - Price * .20<br /> WHEN Quantity >=30 THEN Price - Price * .40<br /> END<br /> ,0)<br /> AS Money)<br />)<br /> <br /> <br />INSERT INTO @tmp values(4, 4000)<br />INSERT INTO @tmp values(16, 3500)<br />INSERT INTO @tmp values(12, 5000)<br />INSERT INTO @tmp values(22, 6620)<br />INSERT INTO @tmp values(5, 6400)<br />INSERT INTO @tmp values(21, 1000)<br />INSERT INTO @tmp values(37, 7000)<br /><br />SELECT * FROM @tmpLaquedasnoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-45960381015892600392010-04-10T05:49:10.846-07:002010-04-10T05:49:10.846-07:00Or you could use another statement in your switch....Or you could use another statement in your switch...<br /><br />DECLARE @tmp TABLE(Quantity integer,<br />Price decimal,<br />DiscountedPrice AS CAST (<br /> CASE<br /> WHEN Quantity BETWEEN 10 and 20 THEN Price - Price * .10<br /> WHEN Quantity BETWEEN 20 and 30 THEN Price - Price * .20<br /> WHEN Quantity >=30 THEN Price - Price * .40<br /> WHEN Quantity IS NULL THEN 0<br /> END<br /> AS Money)<br />)tripyhttps://www.blogger.com/profile/15661997615089542760noreply@blogger.com