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
/*
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:
<< Home
inventory journal
operation software
director software
driver software
factory software
pub software
baker software
baker software
county software
dancing log
operation software
director software
driver software
factory software
pub software
baker software
baker software
county software
dancing log
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
