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:
CREATE TABLE Sales(
[ID] INT NOT NULL,
[Income] INT NULL,
[Visits] INT NULL
);
INSERT INTO Sales([ID],[Income],[Visits])
VALUES
(1,1200,30),
(2,0,22),
(3,800,1),
(4,749,7),
(5,192,0),
(6,974,10);

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

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