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

Comments:
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

<< Home

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