title: Problem using a CTE in a table valued function
body: <p>I am trying to avoid using a scalar valued functions in my project, so I decided to try and convert one of them into a table valued function using a CTE.</p>
  
  <p>I understand that the performance of scalar valued functions is poor because it they have to be executed for each row, and SQL server cannot optimise it in any way (i.e. it acts as a black box).</p>
  
  <p>Here is my first attempt at converting it into a table valued function...</p>
  
  <pre><code>CREATE FUNCTION [dbo].[fn_get_job_average] (@jobnumber VARCHAR(50))
  RETURNS TABLE AS RETURN
  (   
  
    WITH JobAverage AS
    (
      SELECT job.Jobnumber, CAST(AVG(CAST(jobMark.Mark AS DECIMAL(18,1))) AS DECIMAL(18,1)) AS Average
      FROM  job 
        INNER JOIN jobMark 
          ON job.Guid = jobMark.Guid
      WHERE job.Jobnumber = @jobnumber
      GROUP BY job.Jobnumber
    )
    SELECT Jobnumber,
      CASE
        WHEN EXISTS(SELECT * FROM JobAverage) THEN Average
        ELSE 0.0 -- This never executes???, i.e. for job records that don't have a mark nothing is returned
      END AS Average
    FROM JobAverage
  )
  </code></pre>
  
  <p>I want to output a table with the job number and average score.</p>
  
  <p>For jobs that do have the mark, it appears to be OK. That is, the average is returned along with the jobnumer.</p>
  
  <p>For jobs that do not have a mark, it seems to go wrong. The ELSE part of the statement does not execute. That is, I don't get 0.0 returned as a the job average. No records are returned. Am I missing something?</p>
  
  <p>Sorry I am not an experienced SQL developer, so I might have a few glaring mistakes in the above code. However, I am confused why a it doesn't work.</p>
  
  <p>Thanks in advance.</p>