T-SQL 2005 - Divide by zero error encountered

Question

Hi, I am trying to get some percentage data from a stored procedure using code similar to the line below. Obviously this is going to cause a (Divide by zero error encountered) problem when base.[XXX_DataSetB] returns 0 which may happen some of the time.

Does anyone know how i can deal with this in the most efficient manner?

Note: There would be about 20+ lines looking like the one below...

cast((base.[XXX_DataSetB] - base.[XXX_DataSetA]) as decimal) / 
                            base.[XXX_DataSetB] as [XXX_Percentage]

Answer 1

I'd force the average to null by making the bottom term null instead of zero:

cast((base.[XXX_DataSetB] - base.[XXX_DataSetA]) as decimal) 
     / case when base.[XXX_DataSetB] = 0 then null 
         else base.[XXX_DataSetB] end as [XXX_Percentage] 

Answer 2

I guess it depends on what behaviour you expect when XXX_DataSetB is zero.

If you don't want any rows returned that might cause an error, you can easily filter out those rows by adding where XXX_DataSetB <> 0 to your query.

If you would like problem rows to be NULL then you could use a case statement, or something like this:

cast(([XXX_DataSetB] - [XXX_DataSetA]) as decimal) / nullif([XXX_DataSetB], 0)