Skip to content
Advertisement

Divide by zero exception in SQL Server

I have an update statement which has division operation and one row throws exception

Divide by zero exception

and I see that none of the rows got updated .

How to continue on exception so that rows which doesn’t encounter 0 gets updated and row which has 0 fails? Its just a normal update statement without use of any transaction.

Advertisement

Answer

You would filter out rows where the denominator is zero. So, if your query is:

update t
    set ratio = numerator / denominator;

You would change it to:

update t
    set ratio = numerator / denominator
    where denominator <> 0;

Note that sometimes SQL Server evaluates the division for all rows despite the WHERE clause. So, this is actually safer:

update t
    set ratio = numerator / nullif(denominator, 0)
    where denominator <> 0;
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement