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

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

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)

LIKE THIS..

CREATE FUNCTION GetDistributionTable 
(
    @IntID int,
    @TestID int,
    @DateFrom datetime,
    @DateTo datetime
)
RETURNS 
@Table_Var TABLE 
(
    [Count] int, 
    Result float
)
AS
BEGIN
  WITH T 
    AS (	
		select Ticket_Id,COUNT(1) Result from 
		Customer_Survey
		group by MemberID,SiteId,Ticket_Id
   )
  INSERT INTO @Table_Var ([Count], Result)
  SELECT COUNT(*) AS [Count],
       Result
  FROM   T
  GROUP  BY Result
  RETURN 
END
GO

CTE with if else in UDF 

USE [SchoolDB]
GO

/****** Object:  UserDefinedFunction [dbo].[GetDistributionTable]    Script Date: 24-08-2019 05:17:55 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





alter  FUNCTION [dbo].[GetDistributionTable] 
(
   @DepartmentName varchar(50) = 'Production'
)
RETURNS 
@Table_Var TABLE 
(   NUM int IDENTITY(1,1),
    [ParentEmployeeKey] int, 
    Result float
)
AS
BEGIN
Declare @Table_Vars table
(
    [ParentEmployeeKey] int, 
    Result float
);
insert into @Table_Vars([ParentEmployeeKey])
select COUNT(1) Result from 
        [SchoolDB].[dbo].[DimEmployee] where DepartmentName = @DepartmentName
if(@@rowcount >0)
begin
WITH T 
    AS (    
        select [ParentEmployeeKey],COUNT(1) Result from 
        [SchoolDB].[dbo].[DimEmployee] where DepartmentName = @DepartmentName
        group by [ParentEmployeeKey]
   )
  INSERT INTO @Table_Var ([ParentEmployeeKey], Result)
  SELECT COUNT(*) AS [Count],
       Result
  FROM   T
  GROUP  BY Result
end
else

 WITH T 
    AS (    
        select [ParentEmployeeKey],COUNT(1) Result from 
        [SchoolDB].[dbo].[DimEmployee] where DepartmentName = @DepartmentName
        group by [ParentEmployeeKey]
   )
  INSERT INTO @Table_Var ([ParentEmployeeKey], Result)
  SELECT COUNT(*) AS [Count],
       Result
  FROM   T
  GROUP  BY Result
  RETURN 
END



GO