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

score 79 · Accepted Answer · edited Oct 08 '19 at 15:29

79

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)

edited Oct 08 '19 at 15:29

John Smith

7,243
6
49
61

answered Feb 26 '13 at 10:25

Ivan Golović

8,732
3
25
31

If your CTE is recursive you probably won't be able to rewrite it into the subquery form, so the CTE form may be more than a simple matter of taste. Of course, recursive CTEs can throw the optimizer off if you're not careful: – MattW May 28 '14 at 14:54
1

so used to using a `;` in front of the with part of cte. – JJS Aug 12 '16 at 20:34
@JSS - statements in SQL Server are supposed to be terminated with semicolons. So far this is enforced only in limited areas without breaking backwards compatibility by some constructs - especially newer ones - requiring the preceding statement to be terminated with a semicolon. CTEs are just an example of this. Prepending a CTE with a semi-colon is a slightly hacky way of avoiding this requirement of the language - and it leads to confusion in cases like this where there is no previous statement that would otherwise need terminating. Consistently terminating your statements avoids this. – Richard Abey-Nesbit Feb 10 '20 at 01:54
just to keep something interesting in mind: we can't use maxrecursion 0 inside a udf according to this link: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7c7d5fea-38ad-4bc5-9038-a157e640561f/using-option-clause-within-create-function-statement-for-inline-table-functions?forum=transactsql – luisvenezian Feb 18 '21 at 02:28
@RichardAbey-Nesbit It's worth noting that adding a semicolon to the end query in a single-statement UDF (before the last parenthesis) will be interpreted as multiple statements and cause an error. – Graham May 07 '21 at 17:35

score 19 · Answer 2 · edited Feb 26 '13 at 11:42

19

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

edited Feb 26 '13 at 11:42

iMan Biglari

4,674
1
38
83

answered Feb 26 '13 at 11:24

Ram Das

348
4
15

2

If at all possible one should test both the multi-statement function as you've shown as well as the single-statement `RETURNS TABLE` function as the latter can be inlined and thus sometimes have much better performance (see link at bottom of Ivan G's answer). However, as usual, it all depends--*sometimes*, though rarely, it's faster to use the multi-statement function. – ErikE Jun 24 '13 at 02:46

score 0 · Answer 3 · edited Aug 24 '19 at 12:37

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

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

3 Answers3