Help! Can I control the recursion times of a recursive CTE?

I have a table describing a hierarchy structure and the number of levels is very large, say 10000. Can I control the recursive CTE to get the first 1000 levels? Thanks!

You should be able to with a where clause on some accumulator. For example, take the level column in the following query:

WITH Employee_CTE(EmployeeID, ManagerID, level)
AS
(
SELECT EmployeeID, ManagerID, 1 AS level
FROM HumanResources.Employee as Employee

UNION ALL

SELECT Employee.EmployeeID, Employee.ManagerID, level + 1
FROM HumanResources.Employee as Employee
INNER JOIN Employee_CTE
on Employee.ManagerID= Employee_CTE.EmployeeID
)
SELECT *
FROM Employee_CTE
WHERE level <= 2
ORDER BY level desc

Just vary the value in the where clause to vary the number of times it recurses.

|||

Thank you Louis. But that denpends on the recursion would end properly, and it'll calculate all the levels. If the number of the levels is too large, or there's a loop, there would be a problem. I tried to use the MAXRECURSION hint, but it gave me a syntax error. What I have is:

WITH Employee_CTE(EmployeeID, ManagerID, level)
AS
(
SELECT EmployeeID, ManagerID, 1 AS level
FROM HumanResources.Employee as Employee

UNION ALL

SELECT Employee.EmployeeID, Employee.ManagerID, level + 1
FROM HumanResources.Employee as Employee
INNER JOIN Employee_CTE
on Employee.ManagerID= Employee_CTE.EmployeeID

option (MAXRECURSION 10)
)

Then it told me the syntax for option (MAXRECURSION 10) is wrong. You have any ideas what the right way is?

Thanks!

|||

Yeah, it goes on the query, not the CTE (kind of wierd syntax, but anyhow)

WITH Employee_CTE(EmployeeID, ManagerID, level)
AS
(
SELECT EmployeeID, ManagerID, 1 AS level
FROM HumanResources.Employee as Employee

UNION ALL

SELECT Employee.EmployeeID, Employee.ManagerID, level + 1
FROM HumanResources.Employee as Employee
INNER JOIN Employee_CTE
on Employee.ManagerID= Employee_CTE.EmployeeID

)

SELECT *
FROM Employee_CTE
Option (MAXRECURSION 10)