Retrieve Manager/Subordinate hierarchy from Self-Referencing table

[Fresh Air Rider]

Hi SQL Gurus

Could anyone please explain how the following stored procedure can be
amended to retrieve Subordinates in alphabetical order ?

The example below simply retrives records in the order in which they
were entered.

It sounds very easy but I can't sort it out ?

The following code was taken from Narayana Vyas Kondreddi's website
(http://vyaskn.tripod.com/index.htm)

Consider the employee table of an organization, that stores all the
employee records. Each employee is linked to his/her manager by a
manger ID.
CREATE TABLE dbo.Emp
(
EmpID int PRIMARY KEY,
EmpName varchar(30),
MgrID int FOREIGN KEY REFERENCES Emp(EmpID)
)
GO

CREATE NONCLUSTERED INDEX NC_NU_Emp_MgrID ON dbo.Emp(MgrID)
GO

INSERT dbo.Emp SELECT 1, 'President', NULL
INSERT dbo.Emp SELECT 2, 'Vice President', 1
INSERT dbo.Emp SELECT 3, 'CEO', 2
INSERT dbo.Emp SELECT 4, 'CTO', 2
INSERT dbo.Emp SELECT 5, 'Group Project Manager', 4
INSERT dbo.Emp SELECT 6, 'Project Manager 1', 5
INSERT dbo.Emp SELECT 7, 'Project Manager 2', 5
INSERT dbo.Emp SELECT 8, 'Team Leader 1', 6
INSERT dbo.Emp SELECT 9, 'Software Engineer 1', 8
INSERT dbo.Emp SELECT 10, 'Software Engineer 2', 8
INSERT dbo.Emp SELECT 11, 'Test Lead 1', 6
INSERT dbo.Emp SELECT 12, 'Tester 1', 11
INSERT dbo.Emp SELECT 13, 'Tester 2', 11
INSERT dbo.Emp SELECT 14, 'Team Leader 2', 7
INSERT dbo.Emp SELECT 15, 'Software Engineer 3', 14
INSERT dbo.Emp SELECT 16, 'Software Engineer 4', 14
INSERT dbo.Emp SELECT 17, 'Test Lead 2', 7
INSERT dbo.Emp SELECT 18, 'Tester 3', 17
INSERT dbo.Emp SELECT 19, 'Tester 4', 17
INSERT dbo.Emp SELECT 20, 'Tester 5', 17
GO

CREATE PROC dbo.ShowHierarc hy
(
@Root int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @EmpID int, @EmpName varchar(30)

SET @EmpName = (SELECT EmpName FROM dbo.Emp WHERE EmpID = @Root)
PRINT REPLICATE('-', @@NESTLEVEL * 4) + @EmpName

SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root)

WHILE @EmpID IS NOT NULL
BEGIN
EXEC dbo.ShowHierarc hy @EmpID
SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root AND
EmpID > @EmpID)
END
END
GO
EXEC dbo.ShowHierarc hy 1
GO
---President
------Vice President
---------CEO
---------CTO
------------Group Project Manager
---------------Project Manager 1
------------------Team Leader 1
---------------------Software Engineer 1
---------------------Software Engineer 2
------------------Test Lead 1
---------------------Tester 1
---------------------Tester 2
---------------Project Manager 2
------------------Team Leader 2
---------------------Software Engineer 3
---------------------Software Engineer 4
------------------Test Lead 2
---------------------Tester 3
---------------------Tester 4
---------------------Tester 5

[Erland Sommarskog]

Fresh Air Rider (Fr************ *@Hotmail.com) writes:

Could anyone please explain how the following stored procedure can be
amended to retrieve Subordinates in alphabetical order ?

They say that cursors are evil, but what is evil is iterative solutions,
and you already have an iterative solution. And of all ways to iterate,
in my opinion, the cursor is often the best. So here is a way to
tackle the problem:

CREATE PROC dbo.ShowHierarc hy
(
@Root int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @EmpID int, @EmpName varchar(30)

SET @EmpName = (SELECT EmpName FROM dbo.Emp WHERE EmpID = @Root)
PRINT REPLICATE('-', @@NESTLEVEL * 4) + @EmpName

DECLARE emp_cur CURSOR LOCAL FOR
SELECT EmpID FROM dbo.Emp WHERE MgrID = @Root ORDER BY EmpName
OPEN emp_cur

WHILE 1 = 1
BEGIN
FETCH emp_cur INTO @EmpID
IF @@fetch_status <> 0
BREAK
EXEC dbo.ShowHierarc hy @EmpID
END
DEALLOCATE emp_cur
END
GO

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Fresh Air Rider]

Hi Erland

As I said before, that iterative solution wasn't my own code, it was
taken straight from from Narayana Vyas Kondreddi's website
(http://vyaskn.tripod.com/index.htm).

Your solution looks very interesting and I will certainly give it a
try.

Many thanks for your suggestion.

Best wishes
David

[Erland Sommarskog]

Fresh Air Rider (Fr************ *@Hotmail.com) writes:

As I said before, that iterative solution wasn't my own code, it was
taken straight from from Narayana Vyas Kondreddi's website
(http://vyaskn.tripod.com/index.htm).

Yeah, I know. I added the introduction about evil to defend the use
of cursors.

In fact, had Vyas used a cursor for his example, you would probably
not have asked your question, but found the answer yourself!


--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp