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|
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.
SELECT vparent,vmenuid,vmenuname,0 AS LEVEL FROM dbo.SecurityMenu WHERE vParent = null
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.