Retrieving Hierarchical Data in SQL

Hi,

I am adding the stored procedure to retrieve hierarchical data,
Scenario:
Employees working under manager
**********************************************************

USE [SampleDB]
GO
/****** Object: StoredProcedure [dbo].[ShowHierarchy] Script Date: 01/28/2010 17:45:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[ShowHierarchy]
(
@Root int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @EmpID int, @EmpName varchar(30)
-- SET @EmpName = (SELECT H_Firstname FROM dbo.LM_EmployeeDetails WHERE H_ID = @Root)
(SELECT @EmpName=H_Firstname FROM EmployeeDetails WHERE ID = @Root)
PRINT REPLICATE('-', @@NESTLEVEL * 4) + @EmpName
SET @EmpID = (SELECT MIN(H_ID) FROM EmployeeDetails WHERE Manager_ID = @Root)
WHILE @EmpID IS NOT NULL
BEGIN
EXEC dbo.ShowHierarchy @EmpID
SET @EmpID = (SELECT MIN(H_ID) FROM EmployeeDetails WHERE Manager_ID = @Root AND ID > @EmpID)
print @empid
END
END

***************************************************
Pass employeeId as parameter to get tree structure of subbordinates working under the employee.