title: Passing NULL as Value in Parameter for T-SQL stored procedure
body: <p>I have this stored procedure to which I pass a date parameter which could be NULL. The logic I am trying to get at is when the Date parameter is NULL then max of the date column should be chosen but when the parameter is not NULL then the parameter should be passed as the value. </p>
  
  <pre><code>CREATE PROCEDURE [rdm].[Param_Load_Test2] 
      @Retro_Date DATE
  AS
  BEGIN
      SELECT 
          CASE  
             WHEN @Retro_Date IS NULL 
                THEN MAX(lnd.FILE_DATE) 
                ELSE @Retro_Date 
          END AS TEST_DATE
      INTO 
          #LoadTest1234
      FROM 
          TWV.TABLE_WITH_VALUES AS lnd
      GROUP BY 
          FILE_DATE;
  
      SELECT TEST_DATE 
      FROM #LoadTest1234  
  END
  GO
  </code></pre>
  
  <p>Now when I test the above piece of code with the EXEC statement for NULL case I get wrong values. The Not NULL case works fine.</p>
  
  <pre><code>EXEC rdm.Param_Load_Test2 NULL
  
  &gt;&gt; 2018-03-31
  &gt;&gt; 2018-05-31
  </code></pre>
  
  <p>I am expecting only one value which is 2018-05-31 but it returns 2 records instead. What am I doing wrong ?</p>