• Status: Solved
• Priority: Medium
• Security: Public
• Views: 841

# T-SQL UDF - Divide By Zero

Hi,

Having an issue with the Divide By Zero error whilst calculating a value in a SQL UDF.  Tried using the NULLIF and COALESCE functions, but can't seem to get them in the right context to stop the Divide By Zero.  Anyone help me out on this?  Thx.

Calculation:

@C32 = 0.0195
@C29 = 59
@C28 = 59
@C33 = 0.0193
@AnnualRealReturnStart = 0.039
@C16 = 0.0383

Set @C38 = Round( ((Power((1 + @C32) , (@C29 - @C28)) - 1) / @C33) / ((Power((1 + @AnnualRealReturnStart) , (@C29 - @C28)) - 1) / @C16) ,5)

becomes...

Set @C38 = Round( ((Power((1.0195) , 0)) - 1) / 0.0193) / ((Power((1.039) , 0)) - 1) / 0.0383) ,5)
0
simon_kirk
1 Solution

Billing EngineerCommented:

declare @ONE numeric(10,4)
set @ONE = 1

Set @C38 = Round( ((Power((@ONE + @C32) , (@C29 - @C28)) - @ONE) / @C33) / ((Power((@ONE+ @AnnualRealReturnStart) , (@C29 - @C28)) - @ONE) / @C16) ,5)
0

Commented:
Set @C38 = case when ((Power((1 + @AnnualRealReturnStart) , (@C29 - @C28)) - 1) / @C16) <> 0 then Round( ((Power((1 + @C32) , (@C29 - @C28)) - 1) / @C33) / ((Power((1 + @AnnualRealReturnStart) , (@C29 - @C28)) - 1) / @C16) ,5) else 0 end
0

Author Commented:
angelIII, that revised calculation works great, and now returns a NULL value rather than the error, but I'm intrigued as to why changing the 1 to a variable assigned the value of 1 stops the divide by zero error.
0

Commented:
Create another UDF called "DevByZeroSaver" which accepts one parameter.
The function returns 1 if 0 was given and otherwise return the given number.
Makes it more readable but it could have an impact on performance depending on your data amount.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.