How to avoid the "divide by zero" error in SQL?

sql sql-server sql-server-2005 sql-server-2008
Henrik Staun Poulsen picture Henrik Staun Poulsen · May 14, 2009 · Viewed 858.9k times · Source

I have this error message:

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

What is the best way to write SQL code so that I will never see this error message again?

I could do either of the following:

  • Add a where clause so that my divisor is never zero

Or

  • I could add a case statement, so that there is a special treatment for zero.

Is the best way to use a NULLIF clause?

Is there better way, or how can this be enforced?

Answer

Henrik Staun Poulsen picture Henrik Staun Poulsen · May 14, 2009

In order to avoid a "Division by zero" error we have programmed it like this:

Select Case when divisor=0 then null
Else dividend / divisor
End ,,,

But here is a much nicer way of doing it:

Select dividend / NULLIF(divisor, 0) ...

Now the only problem is to remember the NullIf bit, if I use the "/" key.

Related questions

sql query to return differences between two tables

I am trying to compare two tables, SQL Server, to verify some data. I want to return all the rows from both tables where data is either in one or the other. In essence, I want to show all the …

Read More
Simple DateTime sql query

How do I query DateTime database field within a certain range? I am using SQL SERVER 2005 Error code below SELECT * FROM TABLENAME WHERE DateTime >= 12/04/2011 12:00:00 AM AND DateTime <= 25/05/2011 3:53:04 AM Note that I need to get rows within a certain …

Read More
SET versus SELECT when assigning variables?

What are the differences between the SET and SELECT statements when assigning variables in T-SQL?

Read More