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
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
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 .