How to use a CTE statement in a table-valued function in SQL Server

Question

I have come to understand that some versions of Microsoft OLE DB Provider for SQL Server (mostly on Windows XP) do not support WITH statement. So, I decided to move my SQL statement into a table-valued function, and call it from my application. Now, I'm stuck. How should I use the INSERT INTO statement with WITH? Here's the code I have come with so far, but SQL Server doesn't like it... :-(

CREATE FUNCTION GetDistributionTable  (     @IntID int,     @TestID int,     @DateFrom datetime,     @DateTo datetime ) RETURNS  @Table_Var TABLE  (     [Count] int,      Result float ) AS BEGIN INSERT INTO @Table_Var ([Count], Result) WITH T(Result)      AS (SELECT ROUND(Result - AVG(Result) OVER(), 1)          FROM RawResults WHERE IntID = @IntID AND DBTestID = @TestID AND Time >= @DateFrom AND Time <= @DateTo) SELECT COUNT(*) AS [Count],        Result FROM   T GROUP  BY Result      RETURN  END GO 

Answer 1

Syntax for the CTE in table valued function would be:

CREATE FUNCTION GetDistributionTable  (     @IntID int,     @TestID int,     @DateFrom datetime,     @DateTo datetime ) RETURNS TABLE AS RETURN   (     WITH cte AS     (         SELECT ROUND(Result - AVG(Result) OVER(), 1) Result         FROM   RawResults          WHERE  IntID = @IntID          AND    DBTestID = @TestID          AND    Time >= @DateFrom          AND Time <= @DateTo         )      SELECT  COUNT(*) AS [Count],             Result     FROM    cte     GROUP  BY              Result ) GO 

If possible, you can also omit the CTE (WITH statement), and instead create an inline table valued function that uses subquery:

CREATE FUNCTION GetDistributionTable  (     @IntID int,     @TestID int,     @DateFrom datetime,     @DateTo datetime ) RETURNS TABLE AS RETURN   (     SELECT  COUNT(*) AS [Count],             Result     FROM    (                  SELECT ROUND(Result - AVG(Result) OVER(), 1) Result                  FROM   RawResults                   WHERE  IntID = @IntID                   AND    DBTestID = @TestID                   AND    Time >= @DateFrom                   AND Time <= @DateTo         ) t     GROUP  BY              Result ) GO 

Your example seems to be using a multi-statement TVF (insert and select), when you have a choice try using the inline TVF because the multi-statement TVF can prevent query optimizer in choosing a better execution plan (performance difference explained here)