The example below shows a workaround for a Divide by zero error encountered. error by using the NULLIF Function. NULLIF returns the first expression if the two expressions are not equivalent otherwise a NULL is returned.
Usage: SELECT NULLIF(col1, 0)
IF col1 = 0 then a NULL will be returned.
CREATE TABLE dbo.Products
(
Product VARCHAR(10),
Price NUMERIC(7, 2),
CostPrice NUMERIC(7, 2)
)
INSERT dbo.Products SELECT ‘Hammer’, 12.00, 6.00
INSERT dbo.Products SELECT ‘Nails’, 0.10, 0.05
INSERT dbo.Products SELECT ‘Screws’, 0, 0.05
SELECT Product, (CostPrice / Price) * 100 AS PercentProfit FROM dbo.Products
Will Produce the following Error:
(3 row(s) affected)
Server: Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
SELECT Product, (CostPrice / NULLIF(Price, 0))* 100 AS PercentProfit FROM dbo.Products
Returns:
Product PercentProfit
———- ———————–
Hammer 50.0000000000
Nails 50.0000000000
Screws NULL
(3 row(s) affected)
