Monday, March 14, 2005
Insert Node
Stored Procedure for Inserting a new Employee
/*
Inserts a new Employee into the table
*/
CREATE PROC dbo.InsertNode
(@empid int,
@empName VARCHAR(50),
@MgrId int)
AS
BEGIN
DECLARE @Hkey int
DECLARE @PathVal Numeric(32,0)
BEGIN TRAN
--Get the next Prime Number
SELECT @Hkey = Min(Number) FROM PrimeNumbers
WHERE Number > (SELECT ISNULL(Max(Hkey),0) FROM EmpHierarchy)
IF @MgrID IS NULL OR @MgrId = @EmpId --Root Node
SET @PathVal = @Hkey
ELSE --Get Managers Path and Calculate the path of the new node
SELECT @PathVal = Path * @Hkey FROM EmpHierarchy
WHERE EmpId = @MgrId
INSERT INTO EmpHierarchy
VALUES(@empid, @empName, @MgrId,@Hkey, @PathVal)
IF @@ERROR <> 0
ROLLBACK TRAN
ELSE
COMMIT TRAN
END
Stored Procedure for Inserting a new Employee
/*
Inserts a new Employee into the table
*/
CREATE PROC dbo.InsertNode
(@empid int,
@empName VARCHAR(50),
@MgrId int)
AS
BEGIN
DECLARE @Hkey int
DECLARE @PathVal Numeric(32,0)
BEGIN TRAN
--Get the next Prime Number
SELECT @Hkey = Min(Number) FROM PrimeNumbers
WHERE Number > (SELECT ISNULL(Max(Hkey),0) FROM EmpHierarchy)
IF @MgrID IS NULL OR @MgrId = @EmpId --Root Node
SET @PathVal = @Hkey
ELSE --Get Managers Path and Calculate the path of the new node
SELECT @PathVal = Path * @Hkey FROM EmpHierarchy
WHERE EmpId = @MgrId
INSERT INTO EmpHierarchy
VALUES(@empid, @empName, @MgrId,@Hkey, @PathVal)
IF @@ERROR <> 0
ROLLBACK TRAN
ELSE
COMMIT TRAN
END
