Link to home
Start Free TrialLog in
Avatar of kishore_peddi
kishore_peddi

asked on

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

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  

SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Glad I could be of any help and thanks for the grade !