Recursive Queries in Microsoft SQL Server 2008

Case 1

In human resource management system, each employee is supervised by another employee. To do this, I create a table where I assigned one employee with another in the following way:

Employee ID	Name	Supervisor ID
001	Paroar Habib	Null
002	Sumon Mazumder	001
003	Kalim Uddin	001
004	Jewel Ahmed	002

Case 2

In the same human resource management system, I have to create a dynamic menu to give permission to an individual user. For that reason, I create a table as follows:

vmenuid	vmenuname	vparent
M001	HR System	Null
M002	Payroll	M001
M003	Salary Benefits	M002
M004	Recruitment	M001

In both the cases, there is a recursive situation that happens in both tables. So if I want to create a query to get all the menu details with parent id and steps, then I can write the following queries which are basically called Recursive Queries Using Common Table Expressions.

WITH security_menu_Recursive(Parent,MenuId,MenuName,LEVEL)
AS
(
    SELECT vparent,vmenuid,vmenuname,0 AS LEVEL FROM dbo.SecurityMenu WHERE vParent = null
    UNION ALL
    SELECT vparent,vmenuid,vmenuname,Level + 1 AS LEVEL FROM dbo.SecurityMenu
    INNER JOIN security_menu_Recursive AS smr ON smr.menuid = dbo.SecurityMenu.vParent
)
SELECT parent,menuid,menuname,LEVEL FROM security_menu_Recursive

After executing the code, I got the below data:

Parent	MenuID	MenuName	Level
Null	M001	HR System	0
M001	M002	Payroll	1
M001	M004	Recruitment	1
M002	M003	Salary Benefits	2

If you know anything easier or any alternatives, then please give your best comments.

The post Recursive queries in Microsoft SQL Server 2008 appeared first on crea8ivecode.