Recursive Queries a Replacement to connect by prior.

Recursive Queries are applied to retrieve data which is presented in a hierarchical format.
Here in the below example we can see how to use Common Table Expression(CTE) in Recursive Queries
CTEs can also contain references to themselves. This allows the developer to write complex queries simpler.
CTEs can also be used as Views

Here we go..!!!

Example for Recursive CTE,
DECLARE @Employees TABLE(EmployeeID INT,
LastName VARCHAR(100),
Master INT)
INSERT INTO @Employees VALUES (1,'A',null)
INSERT INTO @Employees VALUES (2,'B',1)
INSERT INTO @Employees VALUES (3,'C',1)
INSERT INTO @Employees VALUES (4,'D',2)
INSERT INTO @Employees VALUES (5,'E',2)
INSERT INTO @Employees VALUES (6,'F',3)
INSERT INTO @Employees VALUES (7,'G',6)
INSERT INTO @Employees VALUES (8,'H',6)
INSERT INTO @Employees VALUES (9,'I',8)
INSERT INTO @Employees VALUES (10,'J',9)

SELECT * FROM @Employees




;WITH Master
AS
(
--main Query
SELECT EmployeeID, LastName, Master
FROM @Employees
WHERE EmployeeID = 3
UNION ALL
--recursive part
SELECT e.employeeID,e.LastName, e.Master
FROM @Employees e INNER JOIN Master m
ON e.Master = m.employeeID
)
SELECT * FROM Master