Click here to Skip to main content
Click here to Skip to main content

Tagged as

Recursive Queries in Microsoft SQL Server 2008

, 10 Nov 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sadeque Sharif
Software Developer Meridian Group
Bangladesh Bangladesh
I am:
Deputy Manager (IT) at Meridian Group.
 
I was:
Assistant Manager (Software Division) at KDS Garment Industries Limited.
Assistant Manager (Software Division) at E-Vision Software Limited.
 
My blog:
crea8ivecode
 
My preferred work area:
ASP.NET & SQL SERVER.
 
My email:
sadeque.sharif@yahoo.com
 
Follow me:
twitter | facebook | linkedin
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
QuestionIs this query work? PinmemberThava Rajan29-Oct-13 19:26 
AnswerRe: Is this query work? PinmemberSadeque Sharif29-Oct-13 21:02 
GeneralRe: Is this query work? PinmemberThava Rajan30-Oct-13 3:37 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.141022.2 | Last Updated 10 Nov 2013
Article Copyright 2013 by Sadeque Sharif
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid