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:

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:

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:

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.