SQL Server에서 테이블 값 함수에 CTE 문을 사용하는 방법

SQL Server에서 테이블 값 함수에 CTE 문을 사용하는 방법

일부 버전의 Microsoft OLE DB Provider for SQL Server(대부분 Windows XP)는WITH진술.그래서 저는 제 SQL 문을 테이블 값 함수로 이동하고 애플리케이션에서 호출하기로 결정했습니다.이제, 난 꼼짝도 안 해요.사용 방법INSERT INTO와의 진술.WITH제가 지금까지 가지고 온 코드가 여기 있는데 SQL Server가 마음에 안 들어요... :-(

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

테이블 값 함수의 CTE 구문은 다음과 같습니다.

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

가능하면 CTE를 생략할 수도 있습니다.WITH문) 대신 하위 쿼리를 사용하는 인라인 테이블 값 함수를 만듭니다.

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

다중 문 TVF는 더 나은 실행 계획을 선택할 때 쿼리 최적화 도구를 사용할 수 없기 때문에 인라인 TVF를 사용해 보는 것이 좋습니다(여기서 설명하는 성능 차이).

이렇게..

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

언급URL : https://stackoverflow.com/questions/15086499/how-to-use-a-cte-statement-in-a-table-valued-function-in-sql-server