Posts

Showing posts with the label SqlServer CTE (Common Table Expression)

SQL SERVER – Simple Example of Recursive CTE

Recursive is the process in which the query executes itself. It is used to get results based on the output of base query. We can use CTE as Recursive CTE (Common Table Expression). You can read my previous articles about CTE by searching at http://search.SQLAuthority.com . Here, the result of CTE is repeatedly used to get the final resultset. The following example will explain in detail where I am using AdventureWorks database and try to find hierarchy of Managers and Employees. USE AdventureWorks GO WITH Emp_CTE AS ( SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate FROM HumanResources.Employee WHERE ManagerID IS NULL UNION ALL SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate FROM HumanResources.Employee e INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID ) SELECT * FROM Emp_CTE GO In the above example Emp_CTE is a Common Expression Table, the base record for the CTE is derived by the first sql query before UNION A