CODEWITHSUNDEEP
t-sqlsql-server-2008
Chad
Chad

Reputation: 24679

Change a CTE SELECT to a table value user defined function

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.

Here's the code:

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;

I get the error:

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

Note that line 8 is:

;WITH cteRecursion

..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

Upvotes: 1

Views: 2982

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 6

Related Questions