CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresdivisiondate-math
B. Clay Shannon-B. Crow Raven
B. Clay Shannon-B. Crow Raven

Reputation: 10267

How can I prevent divide-by-zero errors in a Stored Procedure?

I tried to execute my brand-spanking-new Stored Procedure from Server Explorer, I got:

Msg 8134, Level 16, State 1, Procedure duckbilledPlatypi, Line 13 Divide by zero error encountered.

Here is the line implicated (13):

@Week1End Week1End,

...and in more context:

DECLARE 
@Week1End datetime,
@Week2begin datetime

Select Ind.Description,
    @BegDate  BegDate,
    @Week1End Week1End,
    @Week1End Week2Begin,
    @EndDate EndDate,

For full disclosure/context, here is the entire SP, as it now exists:

CREATE PROCEDURE [dbo].[duckbilledPlatypi]
    @Unit varchar(25),
    @BegDate datetime,
    @EndDate datetime
AS

DECLARE 
@Week1End datetime,
@Week2begin datetime

Select Ind.Description,
    @BegDate  BegDate,
    @Week1End Week1End,
    @Week1End Week2Begin,
    @EndDate EndDate,
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.QtyShipped ELSE 0 END) 
Week1Usage,
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.QtyShipped ELSE 0 END) 
Week2Usage,
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) 
Week1Price,
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Price ELSE 0 END) -
   SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.QtyShipped ELSE 0 END) 
UsageVariance,
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.QtyShipped ELSE 0 END)  -
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) 
PriceVariance,
    (SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.QtyShipped ELSE 0 
END)  -
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) )
    / SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) 
PercentageOfPriceVariance
    From    InvoiceDetail Ind
    Where   Ind.Unit = @Unit
    AND Ind.InvoiceDate BETWEEN @BegDate AND @EndDate
    Group By Ind.Description

My guess is that @Week1End Week1End is failing because it is not being given a value and is thus null. I don't know if I should do something like this to prevent the 0s/nulls:

DECLARE 
@Week1End datetime = BegDate + 6;
@Week2begin datetime = BegDate + 7;

...or something like this:

Select Ind.Description,
    @BegDate  BegDate,
    @Week1End BegDate+6,
    @Week2Begin BegDate+7,
    @EndDate EndDate,

...or something else altogether.

As you can probably tell, I don't know how the date math should actually be accomplished. What the dates really need to be are:

Week1Begin is the "BegDate" the user provided as a parameter
Week1End needs to be six days after Week1Begin
Week2Begin needs to be seven days after Week1Begin
Week2End is the "EndDate" the user provided as a parameter

e.g., if the user entered "12/27/2015" for BegDate and "1/6/2016" for EndDate, these values need to be:

Week1Begin = 12/27/2015
Week1End = 1/2/2016
Week2Begin = 1/3/2016
Week2End = 1/6/2016 (week2 is only a four-day week in this case, due to what the user entered)

What should I do to assure that there are no div by zeros occurring here?

Upvotes: 3

Views: 905

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

You could use NULLIF:

   (SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.QtyShipped ELSE 0 
END)  -
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) )
    / NULLIF(SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END),0) 
PercentageOfPriceVariance

So when divisor is 0 the enitre result will be NULL. If you need special value for that case add COALESCE:

COALESCE(exp1 / NULLIF(exp2, 0), special_value) AS result

LiveDemo

Upvotes: 3

Related Questions