April 7, 2007 at 2:32 am
Dear Friends,
In our server we have five databases according to the region wise. We have a employee table which is same as standard employee table. EmpId varchar(25), EmpName varchar(255),ManagerID varchar(25),DeletedSatus bit. We have stored procedure which when executed will list all the employees first manager and his subordinates dept wise and based on the standard conditions coded in the stored procedure. For the past three years we are executing this and there was no problem till now. But our junior database administrator modified some employee's information and we dont have a system to trace it. But the problem now is when ever we run the stored procedure its running infinitely. From my observation i think he might have modified the table in this manner
EmpID EmpName ManagerID DeletedStatus
E001 Smith 0 0
E002 Alex E005 0
E003 Raju E001 0
E004 Mark E002 0
E005 Rakesh E004 0
Suppose if we are retreiving the data.
E002's manager is E005 and E005 manager is E004 and E004 manager is E002. Which is creating a circular loop. Our table has nearly 3000 employee record's and iam unable to trace where this is happening. Plz help me trace where it is getting into an infinite loop. Thankyou in advance.
Chandra Mohan N
[font="Verdana"]Thanks
Chandra Mohan[/font]
April 7, 2007 at 3:59 am
You want help debugging a stored procedure that you didn't post the code for?
April 7, 2007 at 4:08 am
Dear Michael,
It just calculates bonus, taxes etc....
[font="Verdana"]Thanks
Chandra Mohan[/font]
April 7, 2007 at 6:16 am
Check for CIRCULAR REFERENCE!!!
Either an employee who has herself/himself as manager, or a larger chain fo managers that creates a "circle".
N 56°04'39.16"
E 12°55'05.25"
April 7, 2007 at 6:44 am
Dear Peter,
Yes my problem is also the same. But when I had checked the table with the condition.
select * from employee where empid=managerid. But i did not get any results. But I think there is larger chain. But is there any method to find it out.
[font="Verdana"]Thanks
Chandra Mohan[/font]
April 7, 2007 at 6:48 am
You also need to check, for example
Employee A reports to Employee B.
Employee B reports to Employee A.
or
Employee A reports to Employee B.
Employee B reports to Employee C.
Employee C reports to Employee A.
Do you follow me?
N 56°04'39.16"
E 12°55'05.25"
April 7, 2007 at 6:53 am
Dear Peter,
Yes I had understood. But I had nearly 15 levels of employees. So is there any method to find out where it forming a chain.
Thanks in advance
Chandra Mohan N
[font="Verdana"]Thanks
Chandra Mohan[/font]
April 7, 2007 at 7:03 am
Try something like this!
-- Prepare sample data
DECLARE @Sample TABLE (EmpID VARCHAR(4), EmpName VARCHAR(10), ManagerID VARCHAR(4), DeletedStatus INT)
INSERT
@Sample
SELECT 'E001', 'Smith', '0', 0 UNION ALL
SELECT 'E002', 'Alex', 'E005', 0 UNION ALL
SELECT 'E003', 'Raju', 'E001', 0 UNION ALL
SELECT 'E004', 'Mark', 'E002', 0 UNION ALL
SELECT 'E005', 'Rakesh', 'E004', 0
-- Get all "good" employees
DECLARE @Stage TABLE (Generation INT, EmpID VARCHAR(4), ManagerID VARCHAR(4))
DECLARE
@Generation INT
SELECT
@Generation = 0
INSERT
@Stage
SELECT 0,
EmpID,
ManagerID
FROM @Sample
WHERE ManagerID = '0'
WHILE @@ROWCOUNT > 0
BEGIN
SET @Generation = @Generation + 1
INSERT @Stage
SELECT @Generation,
s.EmpID,
s.ManagerID
FROM @Sample AS s
WHERE s.ManagerID IN (SELECT x.EmpID FROM @Stage AS x WHERE x.Generation = @Generation - 1) -- Get all "current" managers
AND s.EmpID NOT IN (SELECT y.EmpID FROM @Stage AS y) -- avoid any level of circular reference
END
SELECT
s.EmpID,
s.ManagerID
FROM @Sample AS s
LEFT JOIN @Stage AS r ON r.EmpID = s.EmpID
WHERE r.EmpID IS NULL
N 56°04'39.16"
E 12°55'05.25"
April 7, 2007 at 7:32 am
Dear Peter,
Thanks for your kind reply. You had given procedure for getting the good employees. But is there any way to detect the employees who are forming a chain.
Thanks
Chandra Mohan N
[font="Verdana"]Thanks
Chandra Mohan[/font]
April 7, 2007 at 7:39 am
Yes, the last part posted above
SELECT
s.EmpID,
s.ManagerID
FROM @Sample AS s
LEFT JOIN @Stage AS r ON r.EmpID = s.EmpID
WHERE r.EmpID IS NULL
will get you all employees that are not "good".
Perhaps there is only a few of them?
The output is EmployeeID and ManagerID. You will see which Employee that has gone "bad".
This is also the simplest solution, I think,
There are methods to get each chain, but that require more space and time. Try this first.
N 56°04'39.16"
E 12°55'05.25"
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply