Monday, March 14, 2005
Delete a Single Node
/*
Delete a given node. All Employees reporting to
the deleted employee will now report to the manager of the deleted employee
*/
ALTER PROC dbo.deleteNode
(@EmpId int)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Hkey bigint
DECLARE @MgrId bigint
DECLARE @EmpPath bigint
DECLARE @MgrPath bigint
SELECT @Hkey = Hkey,
@MgrId = ManagerId,
@EmpPath = Path
FROM EmpHierarchy
WHERE EmpId = @EmpId
SELECT @MgrPath = Path
FROM EmpHierarchy
WHERE EmpId = @MgrId
--Delete Employee
DELETE FROM EmpHierarchy
WHERE EmpId = @EmpId
--Update ManagerID of Immediate Subordiantes
UPDATE EmpHierarchy
SET ManagerId = @MgrId
WHERE Path / Hkey = @EmpPath
--Connect subordinates to the immediate superior
UPDATE EmpHierarchy
SET Path = ((Path/@EmpPath)*@MgrPath)
WHERE PAth % @Hkey = 0
END
GO
/*
Delete a given node. All Employees reporting to
the deleted employee will now report to the manager of the deleted employee
*/
ALTER PROC dbo.deleteNode
(@EmpId int)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Hkey bigint
DECLARE @MgrId bigint
DECLARE @EmpPath bigint
DECLARE @MgrPath bigint
SELECT @Hkey = Hkey,
@MgrId = ManagerId,
@EmpPath = Path
FROM EmpHierarchy
WHERE EmpId = @EmpId
SELECT @MgrPath = Path
FROM EmpHierarchy
WHERE EmpId = @MgrId
--Delete Employee
DELETE FROM EmpHierarchy
WHERE EmpId = @EmpId
--Update ManagerID of Immediate Subordiantes
UPDATE EmpHierarchy
SET ManagerId = @MgrId
WHERE Path / Hkey = @EmpPath
--Connect subordinates to the immediate superior
UPDATE EmpHierarchy
SET Path = ((Path/@EmpPath)*@MgrPath)
WHERE PAth % @Hkey = 0
END
GO
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.
www.templatedesk.com
Post a Comment
www.templatedesk.com
<< Home
