CODEWITHSUNDEEP
sql-server-2005t-sql
sriramjitendra
sriramjitendra

Reputation: 497

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

When I 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

Upvotes: 0

Views: 2375

Answers (3)

Andriy M
Andriy M

Reputation: 77687

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?

Upvotes: 2

marc_s
marc_s

Reputation: 754538

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

Upvotes: 1

user225879
user225879

Reputation: 11

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.

Upvotes: 0

Related Questions