Recurssive common table expressions(Recurssive CTE's)

--Recursive Common Table Expression Examples:

with dimemp(employeekey,firstname,lastname,
parentemployeekey,pfirstname,plastname,employeerank)
as
(
select employeekey,firstname,lastname,parentemployeekey,
CAST('null' as VARCHAR(50)) as pfirstname,
CAST('null' as VARCHAR(50)) as plastname,1 as employeerank from dimemployee where ParentEmployeeKey is null
union all select e.employeekey,e.firstname,e.lastname,e.parentemployeekey,
cast(cte_emp.firstname as varchar(50)) as pfirstname,
CAST(cte_emp.lastname as VARCHAR(50)) as plastname,cte_emp.employeerank+1 as employeerank
from dimemployee e inner join dimemp cte_emp on e.parentemployeekey=cte_emp.employeekey)
select * from dimemp order by employeerank
go