how to handle Zero divided by zero in sqlserver

Question

how we can handle 0/0 in SQL Server? I used select nullif(0)/nullif(0) but ..

Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.

update generalRegionWise set [Collection %] = convert(VARCHAR,convert(MONEY,100.0 * ([Total Collection]) / isnull(nullif([Billed Amount], 0), 0)),1) + '%' The [Total Collection] and [Billed Amount] both are dynamic and can contain 0,0 values — Muhammad Tahir, Apr 17 '15 at 06:51
possible duplicate of [How to avoid the "divide by zero" error in SQL?](http://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql) — sqluser, Apr 17 '15 at 07:00

ughai · Accepted Answer · 2015-04-17T07:02:09.023

Use NULLIF like this something like this

SELECT 0/NULLIF(0,0)

If you want the result as 0 when you encounter NULL, then use ISNULL/COALESCE

SELECT COALESCE(0/NULLIF(0,0),0)

In your query, you only need to move the ISNULL to check like this isnull(([Total Collection]) / nullif([Billed Amount], 0), 0)

update generalRegionWise set [Collection %] = 
    convert(VARCHAR,convert(MONEY,100.0 * isnull(([Total Collection]) / nullif([Billed Amount], 0), 0)),1) + '%'

It is recommended to specify the length when converting to VARCHAR like this convert(VARCHAR(50),... In your case it will take the default length of 30.

score 1 · Answer 2 · answered Apr 17 '15 at 07:02

here are a few more examples.

DECLARE @numerator Int = 0
DECLARE @denominator Int = 0

SELECT ISNULL(NULLIF(@numerator, @denominator),0) AS Result;

SELECT CASE WHEN @denominator = 0 
       THEN 0 
       ELSE @numerator / @denominator 
       END AS Results

Hope this helps.

how to handle Zero divided by zero in sqlserver

2 Answers2