SQL Server snippets

How to prevent divide 0 error in SQL Server

Let suppose you have this Sales table from which you want to calculate the ratio between Income and number of Visits.

You can use the following query to generate the correct result:

[Income] INT NULL,
[Visits] INT NULL
INSERT INTO Sales([ID],[Income],[Visits])
SQL Server snippet 1

If we simply write a query with [Income]/[Visits] we will get an error, as ID=5 row has 0 in the Visits column.

SELECT [ID],[Income]/[Visits]
FROM Sales
Msg 8134 Level 16 State 1 Line 1
Divide by zero error encountered.

To prevent this error, it is a best practice to use the NULLIF function that will output NULL when a zero will be found as denominator.

SELECT [ID],[Income]/NULLIF([Visits],0)
FROM Sales
SQL Server snippet 2

You can find an interactive version of this example following this link .

Back to SQL Server cookbook page