SQL Divide values of columns UNLESS a value is 0

Question

I'm having difficulty working out how to 'error handle' as such the following SQL.

As part of my SELECT statment I have

((sp.sp_retailprice - sp.sp_wholesaleprice_l1) / (sp.sp_wholesaleprice_l1))  as 'calc test'

Which subtracts one column from another then divides the result by the column sp_wholesaleprice_l1.

However, the values of sp_wholesaleprice_l1 are sometimes 0.00, the query returns expected results until it hits a 0.00 and I get a divide by 0 error.

How can I make the query so that if I have a 0.00 in the sp_wholesaleprice_l1 column it does something different... such as replaces the 0 with a 1?

Many thanks :)

Answer 1

(
(sp.sp_retailprice - sp.sp_wholesaleprice_l1) /
(CASE WHEN sp.sp_wholesaleprice_l1 = 0 THEN 1 ELSE sp.sp_wholesaleprice_l1 END)
)  as 'calc test'

More on CASE

http://msdn.microsoft.com/en-us/library/ms181765.aspx