将 CTE SELECT 更改为表值用户定义函数 [英] Change a CTE SELECT to a table value user defined function

问题描述

在较早的帖子中，我在 SO 贡献者的帮助下构建了一个使用 CTE 的 SQL.我现在想将该 SQL 封装在一个用户定义的函数中，该函数返回一个表，但出现以下错误.

In an earlier post, I constructed a SQL that uses a CTE with the help from a SO contributor. I'd like now to encapsulate that SQL in a user defined function that returns a table but I am getting the error below.

代码如下:

Alter FUNCTION GetDescendentSteps 
(   
@StepId INT 
)
RETURNS TABLE 
AS
RETURN
    ;WITH cteRecursion
         AS (SELECT
                 StepId
                 ,1 AS Level
             FROM
                 Step
             WHERE
                 StepId = @StepId
             UNION ALL
             SELECT
                 t.StepId
                 ,c.Level + 1
             FROM
                 Step t
                 INNER JOIN cteRecursion c
                     ON t.ParentStepId = c.StepId
            )
    SELECT
        StepId,Level
    FROM
        cteRecursion
    ORDER BY
        Level,
        StepId;

我收到错误:

Msg 102, Level 15, State 1, Procedure GetDescendentSteps, Line 8
Incorrect syntax near ';'.

注意第 8 行是:

;WITH cteRecursion

..如果我只执行从第 8 行开始的 SQL(在我用文字替换变量 @StepId 之后).

..and that if I execute only the SQL beginning at line 8 (after I replace the variable @StepId with a literal).

此外，这个简单的例子也有效:

Also, this simple example works:

ALTER FUNCTION GetDescendentSteps 
(   
@StepId INT 
)

RETURNS TABLE 

AS
RETURN
select 7 As Stepid,1 As Level

推荐答案

删除第一个 ; 和 order by 子句.

Remove the first ; and the order by clause.

Alter FUNCTION GetDescendentSteps 
(   
@StepId INT 
)
RETURNS TABLE 
AS
RETURN
    WITH cteRecursion
         AS (SELECT
                 StepId
                 ,1 AS Level
             FROM
                 Step
             WHERE
                 StepId = @StepId
             UNION ALL
             SELECT
                 t.StepId
                 ,c.Level + 1
             FROM
                 Step t
                 INNER JOIN cteRecursion c
                     ON t.ParentStepId = c.StepId
            )
    SELECT
        StepId,Level
    FROM
        cteRecursion

这篇关于将 CTE SELECT 更改为表值用户定义函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！