title: Change a CTE SELECT to a table value user defined function
body: <p>In an <a href="https://stackoverflow.com/questions/7679065/self-joining-query-to-find-descendants-of-a-node-syntax-issue">earlier post</a>, 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.</p>
  
  <p>Here's the code:</p>
  
  <pre><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;
  </code></pre>
  
  <p>I get the error:</p>
  
  <pre><code>Msg 102, Level 15, State 1, Procedure GetDescendentSteps, Line 8
  Incorrect syntax near ';'.
  </code></pre>
  
  <p>Note that line 8 is:</p>
  
  <pre><code>;WITH cteRecursion
  </code></pre>
  
  <p>..and that if I execute only the SQL beginning at line 8 (after I replace the variable @StepId with a literal).</p>
  
  <p>Also, this simple example works:</p>
  
  <pre><code>ALTER FUNCTION GetDescendentSteps 
  (   
  @StepId INT 
  )
  
  RETURNS TABLE 
  
  AS
  RETURN
  select 7 As Stepid,1 As Level
  </code></pre>