How to solve Divide by Zero exception in SQL stored procedure?

When I 开发者_StackOverflow社区am executing my stored procedure it is throwing Divide By zero Exception. Because In my database there is no data for some months. How can I solve this?

Below is the my query.

@diffNSVY FLOAT = 0      --I have declared @diffNSVY as optional parameter.

SET @diffNSVY = CASE 
       WHEN (select top 1 NSV from #temp where rn = 1) < 0 THEN 0 
          ELSE (((select top 1 NSV from #temp where descrn = 1) - (select top 1 NSV from #temp where rn = 1))*1.0)/(select top 1 NSV from #temp where rn = 1)  
       END

I am inserting a result set into #temp table. NSV is a column name. rn is rownumber.descrn is also row number in decreasing order.

How can I modify my query?

Please reply.

Regards,

NSJ

First, I would rebuild your script so I didn't need to repeat complex expressions (the subselects, to be precise) more than once.

If possible, use SELECT instead of SET, like this:

SELECT @diffNSVY = CASE
   WHEN rn.NSV < 0 THEN 0
   ELSE (descrn.NSV - rn.NSV) * 1.0 / rn.NSV  /* extra '()' are removed as unneeded */
FROM
  (select top 1 NSV from #temp where rn = 1) AS rn,
  (select top 1 NSV from #temp where descrn = 1) AS descrn

Next, ask yourself, what the result should be in case of division by zero. Should it be zero as well? Then the next optimisation step would be simply this:

SELECT @diffNSVY = CASE
   WHEN rn.NSV <= 0 THEN 0  /* changed '<' to '<=' to account for division by zero */
   ELSE (descrn.NSV - rn.NSV) * 1.0 / rn.NSV
FROM
  (select top 1 NSV from #temp where rn = 1) AS rn,
  (select top 1 NSV from #temp where descrn = 1) AS descrn

But if you wish the result to become undefined (NULL) so you process it later, here's how you can achieve this:

SELECT @diffNSVY = CASE
   WHEN rn.NSV < 0 THEN 0
   ELSE (descrn.NSV - rn.NSV) * 1.0 / CASE rn.NSV WHEN 0 THEN NULL ELSE rn.NSV END
FROM
  (select top 1 NSV from #temp where rn = 1) AS rn,
  (select top 1 NSV from #temp where descrn = 1) AS descrn

Generally, I find this pattern useful when I need to secure myself from divide-by-zero errors. I often use it like this:

...ISNULL(some_expression / CASE @Value WHEN 0 THEN NULL ELSE @Value END, 0)...

Sometimes I use it without ISNULL in which case I process the possible NULL result later using some more sophisticated logic.

EDIT: Oh, and one more thing: with SELECT you can have several assignments at once, like this:

SELECT
  @Var1 = expression1,
  @Var2 = expression2,
  ...

Could this possibly help you to simplify your query too?

That expression of yours is very unclear and hard to understand, and you're selecting the same value several times which is totally unnecessary - so my recommendation would be:

• try to first determine all the bits and pieces that might go into your calcuation - put the results of those select top 1 .... queries into variables

• then check before you divide by zero, and if you divisor would be zero, you need to think of another solution / another value to use instead...

Your problem is this: you're currently only checking for your one value being < 0 and then your return 0 - otherwise (including when that value is = 0) you return an expression which is a division by exactly that value.... You need to add one more special case: if that value is = 0, you cannot use your expression since that results in the divide by zero exception - you need to return some other value in this case!

So your code would be something like:

DECLARE @diffNSVY FLOAT = 0      --I have declared @diffNSVY as optional parameter.

DECLARE @RNValue INT
SET @RNValue = (SELECT TOP 1 NSV FROM #temp WHERE rn = 1) 

DECLARE @DescRNValue INT
SET @DescRNValue = (SELECT TOP 1 NSV FROM #temp WHERE descrn = 1)


SET @diffNSVY = 
   CASE 
      WHEN @RNValue < 0 THEN 0 
      WHEN @RNValue = 0 THEN ....... <-- you need to define a value here! CAnnot divide by @RNValue if it's ZERO !
      ELSE ((@DescRNValue - @RNValue)  * 1.0) / @RNValue
   END

Maybe you need a '<=0' not '<0' in your if clause? You should also probably make sure there's data in your temp table that meets the rn = 1 criteria, otherwise the selection will return null. If all else fails Sql2005 has try catch blocks, so you can catch the divide by zero exception.