Monday, March 14, 2005
Update Node
/*
Update an Employee with a new Manager. Also adjusts the path of the
Employee and his Subordinates
*/
CREATE PROC dbo.UpdateNode
(@Empid bigint,
@NewMgrId bigint)
AS
BEGIN
SET NOCOUNT ON
DECLARE @CurrentMgrPath bigint
DECLARE @NewMgrPath bigint
DECLARE @CurrentMgr bigint
DECLARE @CurrentPath bigint
DECLARE @NewPath bigint
--Employee Exists?
IF NOT EXISTS(SELECT 1 FROM EmpHierarchy WHERE EmpId = @empid)
RAISERROR('Employee Id %d Does not exists',16,1,@Empid)
--Manager Exists
IF NOT EXISTS(SELECT 1 FROM EmpHierarchy WHERE EmpId = @NewMgrId)
RAISERROR('Manager %d Does not exists',16,1,@NewMgrId)
--Get the required values to variables
SELECT @CurrentMgr = ManagerId,
@CurrentPath = Path
FROM EmpHierarchy
WHERE EmpId = @empid
SELECT @CurrentMgrPath = Path
FROM EmpHierarchy
WHERE EmpId = @CurrentMgr
SELECT @NewMgrPath = Path
FROM EmpHierarchy
WHERE EmpId = @NewMgrId
--Cyclic Reference
IF EXISTS(SELECT 1 FROM EmpHierarchy WHERE EmpId = @NewMgrId
AND Path % @CurrentPath = 0)
RAISERROR('Error: Cyclic Reference',16,1)
--Seems OK. Update.
BEGIN TRAN
--Update Manager
UPDATE EmpHierarchy
SET ManagerId = @NewMgrId
WHERE EmpId = @Empid
IF @@ROWCOUNT = 0 OR @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR('Failed To update',16,1)
END
--Update Path
UPDATE EmpHierarchy
SET Path = ((Path / @CurrentMgrPath)*@NewMgrPath)
WHERE Path % @CurrentPath = 0
IF @@ROWCOUNT = 0 OR @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR('Failed To update',16,1)
END
ELSE
BEGIN
COMMIT
RETURN 1
END
END
GO
/*
Update an Employee with a new Manager. Also adjusts the path of the
Employee and his Subordinates
*/
CREATE PROC dbo.UpdateNode
(@Empid bigint,
@NewMgrId bigint)
AS
BEGIN
SET NOCOUNT ON
DECLARE @CurrentMgrPath bigint
DECLARE @NewMgrPath bigint
DECLARE @CurrentMgr bigint
DECLARE @CurrentPath bigint
DECLARE @NewPath bigint
--Employee Exists?
IF NOT EXISTS(SELECT 1 FROM EmpHierarchy WHERE EmpId = @empid)
RAISERROR('Employee Id %d Does not exists',16,1,@Empid)
--Manager Exists
IF NOT EXISTS(SELECT 1 FROM EmpHierarchy WHERE EmpId = @NewMgrId)
RAISERROR('Manager %d Does not exists',16,1,@NewMgrId)
--Get the required values to variables
SELECT @CurrentMgr = ManagerId,
@CurrentPath = Path
FROM EmpHierarchy
WHERE EmpId = @empid
SELECT @CurrentMgrPath = Path
FROM EmpHierarchy
WHERE EmpId = @CurrentMgr
SELECT @NewMgrPath = Path
FROM EmpHierarchy
WHERE EmpId = @NewMgrId
--Cyclic Reference
IF EXISTS(SELECT 1 FROM EmpHierarchy WHERE EmpId = @NewMgrId
AND Path % @CurrentPath = 0)
RAISERROR('Error: Cyclic Reference',16,1)
--Seems OK. Update.
BEGIN TRAN
--Update Manager
UPDATE EmpHierarchy
SET ManagerId = @NewMgrId
WHERE EmpId = @Empid
IF @@ROWCOUNT = 0 OR @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR('Failed To update',16,1)
END
--Update Path
UPDATE EmpHierarchy
SET Path = ((Path / @CurrentMgrPath)*@NewMgrPath)
WHERE Path % @CurrentPath = 0
IF @@ROWCOUNT = 0 OR @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR('Failed To update',16,1)
END
ELSE
BEGIN
COMMIT
RETURN 1
END
END
GO
Comments:
<< Home
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
