January 7, 2009 at 7:07 am
I'm trying to implement a table similar to the classic EMPLOYEE table with MANAGER column. For simplicity, let's just say the table has 2 columns:
EmpID
ManID (nullable)
In my Assign_Manager proc, I first need to check if the person I'm assigning to does not have the current EMPLOYEE as their manager (no circular join). In fact, I need to check the entire hierarchy for that user to make sure my ID does not show up. For example:
EmpID ManID
1 NULL
2 1
3 2
Now if I want to Assign 3 as the Manager of 1, it should fail because it would create a loop.
MY QUESTION: Is there a set based solution to check for this? I can't think of one. If not, what's the best approach? A while loop that breaks out when I find the ID I'm looking for or a recursive function?
Thanks
January 7, 2009 at 9:01 am
A recursive CTE could find that for you. Use that with an If Exists check and roll back the transaction on violation.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 11, 2009 at 10:45 am
This worked great! Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply