title: How can I prevent divide-by-zero errors in a Stored Procedure?
body: <p>I tried to execute <a href="https://stackoverflow.com/questions/34772214/where-is-the-incorrect-syntax-near-sum-in-this-sp">my brand-spanking-new Stored Procedure</a> from Server Explorer, I got:</p>
  
  <p><em>Msg 8134, Level 16, State 1, Procedure duckbilledPlatypi, Line 13
  Divide by zero error encountered.</em></p>
  
  <p>Here is the line implicated (13):</p>
  
  <pre><code>@Week1End Week1End,
  </code></pre>
  
  <p>...and in more context:</p>
  
  <pre><code>DECLARE 
  @Week1End datetime,
  @Week2begin datetime
  
  Select Ind.Description,
      @BegDate  BegDate,
      @Week1End Week1End,
      @Week1End Week2Begin,
      @EndDate EndDate,
  </code></pre>
  
  <p>For full disclosure/context, here is the entire SP, as it now exists:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>DECLARE 
  @Week1End datetime = BegDate + 6;
  @Week2begin datetime = BegDate + 7;
  </code></pre>
  
  <p>...or something like this:</p>
  
  <pre><code>Select Ind.Description,
      @BegDate  BegDate,
      @Week1End BegDate+6,
      @Week2Begin BegDate+7,
      @EndDate EndDate,
  </code></pre>
  
  <p>...or something else altogether.</p>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>e.g., if the user entered "12/27/2015" for BegDate and "1/6/2016" for EndDate, these values need to be:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>What should I do to assure that there are no div by zeros occurring here?</p>