title: Different results from query and stored procedure in T-SQL
body: <p>Good day!</p>
  
  <p>I am trying to do some minor null ratio analysis on some tables for our client and I wanted to create a stored procedure to make this easier to automate. </p>
  
  <p>The problem I am running into is that when using the query by itself it gives the correct result but when using the stored procedure the result is different.</p>
  
  <p><strong>Query</strong></p>
  
  <pre><code>with cte as 
  (SELECT sum(case when [EstateBOA] is NULL then 1 else 0 end) as "Null Values",
          sum(case when [EstateBOA] is not NULL then 1 else 0 end) as "Not-Null Values" 
          FROM [FASTDW].[PROP].[FactBuilding]
  )
  
  SELECT '[EstateBOA]' as ColumnName,*,CAST("Null Values" AS FLOAT) /
  CAST(("Null Values" + "Not-Null Values") AS FLOAT) as "Null ratio"
  FROM cte
  </code></pre>
  
  <p><strong>Stored Procedure</strong> </p>
  
  <pre><code>USE [AUDIT] 
  GO
  /****** Object:  StoredProcedure [dbo].[GetNullStatsFromColumnInBuilding]    
  Script Date: 2018-06-11 11:22:04 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER PROCEDURE [dbo].[GetNullStatsFromColumnInBuilding]
       @Columnname nvarchar(50)
  AS
  ;with cte as 
  (SELECT sum(case when @Columnname is NULL then 1 else 0 end) as "NullValues",
          sum(case when @Columnname is not NULL then 1 else 0 end) as "Not-Null Values" 
      FROM [FASTDW].[PROP].[FactBuilding]
  )
  SELECT @Columnname as ColumnName,*,  CAST("Null Values" AS FLOAT) / 
  CAST(("Null Values" + "Not-Null Values") AS FLOAT) as "Null ratio"
  FROM cte
  </code></pre>
  
  <p><strong>Query Result</strong></p>
  
  <p><a href="https://i.stack.imgur.com/ebr4k.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ebr4k.png" alt="Query result"></a></p>
  
  <p><strong>Stored Procedure Result</strong>
  <a href="https://i.stack.imgur.com/CoeST.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/CoeST.png" alt="Procedure Result"></a></p>
  
  <p>Any idea why this is the case? Is it because of the "with X as" inside the stored procedure? </p>
  
  <p>Thanks for your time.</p>