title: MS SQL 2012 - Divide by zero error encountered
body: <p>I'm using MS SQL 20012 and have a Stored Procedure the code is like this:</p>
  
  <pre><code>USE [StatsAa]
  GO
  
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER PROCEDURE [dbo].[DriverVSComp] 
      -- Add the parameters for the stored procedure here
      @PERSON_NO nvarchar(50)
  AS
      SET NOCOUNT ON;
  
  DECLARE @weeks int = 26
  
  ;WITH Person AS (
     SELECT
       datediff(ww, DATE_GIVEN, getdate())+1 AS Week,
       AVG(CHECK_AMOUNT) AS Person_Check_Amount
     FROM CHECKS
     WHERE PERSON_NO=11  AND DATE_GIVEN &gt;= dateadd(ww, -@weeks, getdate())
     GROUP BY datediff(ww, DATE_GIVEN, getdate()) +1
  )
  
  , Company AS (
      SELECT week,
    AVG (COMPANY_Check_Amount) AS COMPANY_Check_Amount
      FROM (
         SELECT
           datediff(ww, DATE_GIVEN, getdate())+1 AS Week,
           SUM(CHECK_AMOUNT)/COUNT(DISTINCT PERSON_NO) AS COMPANY_Check_Amount
         FROM CHECKS
         WHERE PERSON_NO&lt;&gt;11  AND DATE_GIVEN &gt;= dateadd(ww, -@weeks, getdate())
         GROUP BY datediff(ww, DATE_GIVEN, getdate())+1 
      ) t
    GROUP BY Week
  )
  
  SELECT c.week
   , isnull(Person_Check_Amount,0)  Person_Check_Amount
   , isnull(Company_Check_Amount,0) Company_Check_Amount
  FROM Person p
  FULL OUTER JOIN Company c ON c.week = p.week
  ORDER BY Week DESC
  </code></pre>
  
  <p>but I keep getting this error: </p>
  
  <pre><code>Microsoft OLE DB Provider for ODBC Drivers error '80040e14' 
  
  [Microsoft][SQL Server Native Client 11.0][SQL Server]Divide by zero error encountered. 
  </code></pre>
  
  <p>When I run is from MSSMS it works fine and displays results but only after I ran the following once:</p>
  
  <pre><code>SET ARITHABORT OFF 
  SET ANSI_WARNINGS OFF
  </code></pre>
  
  <p>but the error still exists when I run the page (.asp page)</p>
  
  <p>I was reading that this is because some values are Null but how do I put this into a stored procedure safely ? 
  It's really important that no data is missed/passed/ignored because it has a Null value, because then it would offset the Drivers and the Comp. DATA and AVERAGES. 
  Is it possible to set a zero '0' or the default value when it runs into a NULL ?
  And also, how do I find what column/row is giving it a problem.</p>
  
  <p>As Always, Thanks so much for the help in advance!</p>