Monday, March 14, 2005

 
Print Hierarchy


/*
Print Hierarchy with Graphical representation of the Level
*/

CREATE PROC dbo.PrintHierarchy
@EmpID as bigint = 1
AS
BEGIN
SET NOCOUNT ON
DECLARE @PathVal as bigint
SELECT @PathVal = Path FROM EmpHierarchy
WHERE EmpId = @EmpID

SELECT REPLICATE('-',(SELECT COUNT(*) FROM EmpHierarchy B WHERE A.Path % B.Path = 0)) As Hierarchy,
(SELECT COUNT(*) FROM EmpHierarchy B WHERE A.Path % B.Path = 0) as [Level],
A.EmpName AS Employee, C.EmpName As Manager
FROM EmpHierarchy A
LEFT JOIN EmpHierarchy C
ON A.ManagerId = C.EmpId
WHERE A.Path % @PathVal = 0
ORDER By [Level]
END





Comments:
free themeforest templates from Template Plaza. If you need it, just cheek link and I'll pm you direct download link.
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?