Click here to Skip to main content
13,150,344 members (30,715 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

44.6K views
11 bookmarked
Posted 26 Oct 2013

Recursive Queries in Microsoft SQL Server 2008

, 10 Nov 2013
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 IDNameSupervisor ID
001Paroar HabibNull
002Sumon Mazumder001
003Kalim Uddin001
004Jewel Ahmed002

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:

vmenuidvmenunamevparent
M001HR SystemNull
M002PayrollM001
M003Salary BenefitsM002
M004RecruitmentM001

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:

ParentMenuIDMenuNameLevel
NullM001HR System0
M001M002Payroll1
M001M004Recruitment1
M002M003Salary Benefits2

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:
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

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionThis query doesn't work Pin
Dilshods8-Jan-16 21:06
memberDilshods8-Jan-16 21:06 
Questionmenu hierarchy name Pin
kepulo15-Nov-15 0:26
memberkepulo15-Nov-15 0:26 
QuestionIs this query work? Pin
Thava Rajan29-Oct-13 19:26
memberThava Rajan29-Oct-13 19:26 
AnswerRe: Is this query work? Pin
Sadeque Sharif29-Oct-13 21:02
memberSadeque Sharif29-Oct-13 21:02 
GeneralRe: Is this query work? Pin
Thava Rajan30-Oct-13 3:37
memberThava Rajan30-Oct-13 3:37 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170924.2 | Last Updated 10 Nov 2013
Article Copyright 2013 by Sadeque Sharif
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid