T-SQL UDF - Divide By Zero

Posted on 2006-07-07
Last Modified: 2008-03-03

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.


@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)


Set @C38 = Round( ((Power((1.0195) , 0)) - 1) / 0.0193) / ((Power((1.039) , 0)) - 1) / 0.0383) ,5)
Question by:simon_kirk
LVL 142

Accepted Solution

Guy Hengel [angelIII / a3] earned 500 total points
ID: 17056737

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)
LVL 14

Expert Comment

ID: 17056782
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
LVL 14

Author Comment

ID: 17056954
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.

Expert Comment

ID: 17057104
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.

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
migration MS SQL database to Oracle 30 61
sql 2014,  lock limit 5 32
CPU high usage when update statistics 2 30
Need help with a Stored Proc on Sql Server 2012 4 10
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question