Search code examples
sql-serverquery-optimizationdivide-by-zero

Handle "Divide by zero error encountered error"

    SELECT   ((CASE WHEN (qid2.AgeBelow_16 - qid1.AgeBelow_16)= 0 THEN 1 ELSE (qid2.AgeBelow_16- qid1.AgeBelow_16) END )/ (CASE WHEN [qid1].AgeBelow_16= 0 THEN 1 ELSE [qid1].AgeBelow_16  END))*100 AS AgeBelow_16_Percent, 
    -- otherfields

    FROM
    -- whole query

I am using above query for calculating the percent difference from old value and new value. the main problem is there 0 if any field value 0 then throw "Divide by zero error encountered error" so I am using case when then else end. Is above implementation is good or can be write better then this please suggest me because it is more confusion when fields are huge like above 10.

Solution

  • Something like this perhaps ?

    declare @X int = 0
select 10 / isnull(nullif(@X, 0), 1)