Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Recursive Programming (Hierarchy Levels) - SQL SErver 2000 / 2005  !!

Posted on 2007-09-28
4
Medium Priority
?
333 Views
Last Modified: 2008-01-09
Hi,

I am looking more closely into CTE (Common Table Expressions) in SQL Server 2005. Can you please provide me sample code how we can achieve the below task WITHOUT using CTE.

-- Create a table

CREATE TABLE Employees
(
       EmpId      int,
       Ename      varchar(50),
       ReportsTo      int
)
GO

-- Insert some data

INSERT INTO Employees VALUES (1, 'Kishan', 2)
GO

INSERT INTO Employees VALUES (2, 'Horwitz', NULL)
GO

INSERT INTO Employees VALUES (3, 'James', 2)
GO

INSERT INTO Employees VALUES (4, 'Anurag', 2)
GO

INSERT INTO Employees VALUES (5, 'Gary', 2)
GO

INSERT INTO Employees VALUES (6, 'Bhalaram', 5)
GO

INSERT INTO Employees VALUES (7, 'Manish', 5)
GO

INSERT INTO Employees VALUES (8, 'Jia', 2)
GO

INSERT INTO Employees VALUES (9, 'sitaiah', 5)
GO

-- I need the following output

EmpID    EmpName         ReportsTo    HierarchyLevel
2             Horwitz             NULL                    0
4             Anurag                 2                        1
5             Gary                     2                        1
3             James                   2                        1
8             Jia                         2                        1
1             Kishan                  2                        1
6             Bhalaram              5                        2
7             Manish                  5                        2
9             Sitaiah                  5                        2

Appreciate your time and patience !!

Thanks  

0
Comment
Question by:kishore_peddi
  • 2
4 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 960 total points
ID: 19983159
Create FUNCTION dbo.RetEmpLevel (@EmpId INT ) RETURNS INT
 AS
 BEGIN
       DECLARE @Level INT, @ReportsTo int
       SELECT @ReportsTo = ReportsTo,@Level = 0
       FROM Employees
       WHERE EmpID = @EmpID
       
       WHILE @ReportsTo IS NOT NULL
       BEGIN
             SELECT @ReportsTo = ReportsTo,@Level = @Level+1
             FROM Employees
             WHERE EmpID = @ReportsTo
       
       END
 
       RETURN @Level
 
 END
 
 GO
 
 SELECT *, dbo.RetEmpLevel (EmpId) AS EmpLevel
 FROM Employees
0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 19985471
Hmm, that function will be SLOW on a large dataset.  Our company has 8,000+ employees and one of my staff wrote a function like that and it was brutally slow.  Why don't you want to use the recursive functionality built into SQL 2005?
0
 
LVL 18

Accepted Solution

by:
Yveau earned 1040 total points
ID: 19985561
This will do ...
It is set based per level, so MUCH faster that employee based !

-- create temp table
CREATE TABLE #E
(
       EmpId      int,
       Ename      varchar(50),
       ReportsTo      int,
       HierarchyLevel int
)
GO

declare @level int
select @level = 0

-- insert top level
insert
into    #e
select  *
,       @level
from    employees
where   reportsto is null

-- insert other levels by means of a loop
while (select count(*) from employees e1 left outer join #e e2 on e1.empid = e2.empid inner join #e e3 on e1.reportsto = e3.empid where e2.empid is null) > 0
begin
        select  @level = @level + 1

        insert
        into    #e
        select  e1.*
        ,       @level
        from    employees e1 left outer join #e e2 on e1.empid = e2.empid inner join #e e3 on e1.reportsto = e3.empid where e2.empid is null
end

-- show result:
select * from #e

Hope this helps ...
0
 
LVL 18

Expert Comment

by:Yveau
ID: 19986500
Glad I could be of any help and thanks for the grade !
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question