Recursive CTE in SSMS

We know that CTE is famous for one of it's best features called recursive. Means, at the time of declaring CTE we can call the same inside it.

Will see this features by an example given below.

I am having a table named EMPLOYEE.

Now we have to arrange the employee in to employee hierarchy or level wise from to to bottom.

To solve this we have to use recursive CTE.

;WITH CTE_MANAGER_H AS(

      SELECT EMPLOYEE_ID,NAME,MANAGER_ID, 1 AS E_LEVEL

      FROM DBO.EMPLOYEE

      WHERE EMPLOYEE_ID IS NULL

     

      UNION ALL

     

      SELECT E.EMPLOYEE_ID,E.NAME,E.MANAGER_ID,(E_LEVEL + 1) AS E_LEVEL

      FROM DBO.EMPLOYEE E

      INNER JOIN CTE_MANAGER_H CMH

            ON E.MANAGER_ID = CMH.EMPLOYEE_ID

      WHERE E.EMPLOYEE_ID IS NOT NULL

)

SELECT EMPLOYEE_ID,NAME,E_LEVEL FROM CTE_MANAGER_H ORDER BY E_LEVEL

Output:

Remember maximum recursion level possible for CTE is 100 in default.

We can change it on per-query basis by appending a hint :

OPTION (MAXRECURSION 0)