A common table expression (CTE) provides the significant advantage of being able to reference itself, thereby creating a recursive CTE. A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained.
A query is referred to as a recursive query when it references a recursive CTE. Returning hierarchical data is a common use of recursive queries, for example: Displaying employees in an organizational chart, or data in a bill of materials scenario in which a parent product has one or more components and those components may, in turn, have subcomponents or may be components of other parents.
A recursive CTE can greatly simplify the code required to run a recursive query within a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. In earlier versions of SQL Server, a recursive query usually requires using temporary tables, cursors, and logic to control the flow of the recursive steps.
–create table #Employee(vmenuid varchar(25) not null,vmenuname varchar(25) null,vparent varchar(25) null);
–insert into #Employee values(‘M001′ ,’HR System’, Null),(‘M002’ , ‘Payroll’, ‘M001’),
–(‘M003’, ‘Salary Benefits’, ‘M002’),
–(‘M004′ ,’Recruitment’, ‘M001’)
select * from dbo.#Employee;
WITH security_menu_Recursive(Parent,MenuId,MenuName,LEVEL)
AS
(
SELECT vparent,vmenuid,vmenuname,0 AS LEVEL FROM dbo.#Employee WHERE vParent is NULL
UNION ALL
SELECT vparent,vmenuid,vmenuname,Level + 1 AS LEVEL FROM dbo.#Employee
INNER JOIN security_menu_Recursive AS smr ON smr.menuid = dbo.#Employee.vParent
)
SELECT parent,menuid,menuname,LEVEL FROM security_menu_Recursive
–best query for CTE and declare table example in following link—
http://stackoverflow.com/questions/12827236/tsql-recursive-cte-inefficient-need-an-alternative