Divide by zero error encountered

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)