TSQL coding challenge
Today’s entry is another T-SQL puzzle. Steve Kass took the prize for the best solution to my last T-SQL puzzle, and several others came up with some pretty original solutions of their own. I especially liked the ones that were T-SQL specific – ones that weren’t translated from other languages.
Today’s challenge is to detect whether a cross-row relationship that exists in a particular table has been corrupted. The scenario is this:
You have a table of employee-to-mentor relationships that looks like this:
CREATE TABLE Employees
(EmpId int PRIMARY KEY,
Mentor int REFERENCES Employees(EmpId)
)
Every employee has exactly one mentor and every mentor has exactly one associated employee. The company's first employee, the CEO, has no mentor, so her mentor column is NULL. Here's what a simplified version of the table might look like:
INSERT Employees VALUES (0,NULL)
INSERT Employees VALUES (1,0)
INSERT Employees VALUES (2,1)
INSERT Employees VALUES (3,2)
INSERT Employees VALUES (4,3)
INSERT Employees VALUES (5,4)
INSERT Employees VALUES (6,5)
INSERT Employees VALUES (7,6)
INSERT Employees VALUES (8,7)
INSERT Employees VALUES (9,8)
The most recently added employee doesn’t mentor anyone for obvious reasons. His employee number is tracked in a separate table so that it is readily available as a mentor when another employee is added. When a new employee joins, he is assigned the employee that was previously the last employee as his mentor, regardless of his or her title or rank in the company. There is otherwise no relationship between the rows in the table, and, in particular, employee numbers are not necessarily sequential or related in any way.
The company that uses this table has a stored proc that runs on a nightly basis, determines who is mentoring whom, and, using singleton SELECTs, returns the complete list of mentor-employee pairs beginning with the mostly recently added employee (whose ID is cached in a separate table) and working up to the CEO. The Employee table is so huge that it doesn't attempt to store these relationships anywhere; it just returns each one using simple PRINT commands as it determines them. Due to a bug in the app that updates the table when an employee leaves and a lack of RI on the table itself, the relationships between the employees and mentors occasionally become corrupted. Specifically, an employee higher up in the chain erroneously has his mentor set to someone lower in the chain. In other words, his Mentor column is being set to someone being mentored (perhaps indirectly) by him.
The net effect of this is that the stored procedure that returns the mentor-employee pairs gets stuck in an infinite loop. It never reaches the CEO because the relationship chain leading to her is broken. Here's the code for the proc and a function it uses:
CREATE FUNCTION dbo.Mentor(@EmpId int)
RETURNS int
AS BEGIN
RETURN (SELECT Mentor FROM Employees WHERE EmpId=@EmpId)
END
GO
CREATE PROC ListMentors(@LastEmpIdAdded int) AS
DECLARE @i int
SELECT @i = @LastEmpIdAdded -- ID of last employee added (known in advance)
PRINT 'Employee: '+CAST(@i AS varchar(10))
SELECT @i = dbo.Mentor(@i)
PRINT 'Mentor: '+CAST(@i AS varchar(10))
WHILE (@i IS NOT NULL)
BEGIN
PRINT 'Employee: '+CAST(@i AS varchar(10))
SELECT @i = dbo.Mentor(@i)
PRINT 'Mentor: '+CAST(@i AS varchar(10))
END
And here's some code to simulate the buggy app by intentionally breaking one of the mentor-employee relationships in the sample table:
UPDATE Employees SET Mentor = 7 WHERE EmpId=5
If you run the proc against the sample table after running the above UPDATE, it will get stuck in an infinite loop:
EXEC ListMentors 9 -- 9 is the ID of the last employee added (known in advance)
Your task is to change the proc's singleton SELECT code so that it can detect this type of corruption in the mentor-employee relationships as it traverses the table and return an error message rather than get stuck in an infinite loop. Some rules of the game:
1. Because the company is huge and space on the system is limited, you can’t store values from the Employee table elsewhere (either at the client or on the server, on disk or in memory).
2. For the same reason and because of permissions issues, you can’t modify the table in any way or create any other tables or objects.
3. Your code must run on both SQL Server 2000 and SQL Server 2005.
4. You can't replace the proc's singleton SELECT code with set-oriented code. The company has its reasons for using singleton SELECT logic here, and it's your job to modify the singleton SELECT loop to avoid running infinitely, not rewrite it. This includes changing the logic in the Mentor function. In fact, you can't change the Mentor function at all. Your alterations must be limited to the proc.
5. To keep this interesting, you can't use techniques that rely on counting the number of employees or mentors in advance. For purposes of this exercise, you have no way of knowing how many employees, mentors, or rows are in the table or that meet a particular set of criteria. There may be 10,000 or there may be 10,000,000 -- you have no way of knowing and can't use COUNT() or anything similar to tell you.
6. Because I’ve discussed this with various MS people, if you work for MS (or ever have), you can’t play J
Happy Coding!
Comments
Anonymous
February 03, 2006
I must have missed something... Does this do it?
IF EXISTS
(
SELECT
E1.*
FROM
Employees E1
LEFT JOIN Employees E2 ON
E1.Mentor = E2.EmpID
WHERE
E1.Mentor IS NOT NULL AND
E2.EmpID IS NULL
)
BEGIN
RAISERROR('Something Bad Happened',1,1)
ENDAnonymous
February 03, 2006
It would appear that your example table doesn't allow deletion of an employee record where another employee record has a reference to it as a mentor. Did you mean to add the foreign key constraint to the example?Anonymous
February 03, 2006
Yes, I meant to add it as an FK constraint. You simply adjust the mentor reference to another employee (the mentor of the employee about to be deleted) before deleting a mentor. It is, in fact, a bug in the app that handles this that causes the corrupt relationship chain.
Also, your query doesn't detect the condition I'm describing. Here's some sample data that demonstrates the corrupt chain:
INSERT Employees VALUES (0,NULL)
INSERT Employees VALUES (1,0)
INSERT Employees VALUES (2,1)
INSERT Employees VALUES (3,2)
INSERT Employees VALUES (4,3)
INSERT Employees VALUES (5,4)
select * from Employees
update Employees set Mentor = 5 where EmpId=2
select * from EmployeesAnonymous
February 03, 2006
Ok, as I originally suspected, I wasn't understanding.
Too bad also - since I had cooked up 4 additional methods to detect a missing record to find the lowest-cost method.
Yes, this scenario definitely increases the difficulty :)Anonymous
February 04, 2006
I've revised the post to make this a bit more obvious and to provide some examples that are a bit more concrete rather than leaving so much to the imagination. Hopefully, it makes more sense now.Anonymous
February 05, 2006
My quick solution is to change the function to aviod looping
CREATE FUNCTION dbo.Mentor(@EmpId int)
RETURNS int
AS BEGIN
RETURN (SELECT Mentor FROM Employees WHERE EmpId=@EmpId and 2 > select count(*) from employee where Mentor=@Empid)
END
GO
This solution will ensure that the proc doesn't loop, but it doesn't issue error message for corrupted link.
Let me think more on this.Anonymous
February 05, 2006
pkr171: See rule #5 -- you can't use COUNT() to determine the number of employees, mentors, or rows in any way :-)Anonymous
February 05, 2006
Hi,
I don't know if this is invalidated under rule #5 (similar to COUNT())
ALTER PROC ListMentors(@LastEmpIdAdded int) AS
/* Check the validity of the hierarchy /
if( select nullif(checksum_agg(EmpId),checksum_agg(coalesce(Mentor,@LastEmpIdAdded)))
from employees) is not null
begin
raiserror('Bad hierarchy',16,1)
return 50000
end
/ end check */
DECLARE @i int
SELECT @i = @LastEmpIdAdded -- ID of last employee added (known in advance)
PRINT 'Employee: '+CAST(@i AS varchar(10))
SELECT @i = dbo.Mentor(@i)
PRINT 'Mentor: '+CAST(@i AS varchar(10))
WHILE (@i IS NOT NULL)
BEGIN
PRINT 'Employee: '+CAST(@i AS varchar(10))
SELECT @i = dbo.Mentor(@i)
PRINT 'Mentor: '+CAST(@i AS varchar(10))
END
RegardsAnonymous
February 05, 2006
One fact we know is that MentorID(aka EmpID) has to be smaller than EmpID. So the maximum number of times the while loop getting executed should be less than @LastEmpIdAdded
CREATE PROC ListMentors(@LastEmpIdAdded int)
AS
DECLARE @i int
SELECT @i = @LastEmpIdAdded -- ID of last employee added (known in advance)
PRINT 'Employee: ' + CAST(@i AS varchar(10))
SELECT @i = dbo.Mentor(@i)
PRINT 'Mentor: ' + CAST(@i AS varchar(10))
declare @count int
select @count = 0
WHILE (@i IS NOT NULL)
BEGIN
PRINT 'Employee: ' + CAST(@i AS varchar(10))
SELECT @i = dbo.Mentor(@i)
PRINT 'Mentor: ' + CAST(@i AS varchar(10))
select @count = @count + 1
if @count > @LastEmpIdAdded
begin
raiserror('Error.', 1, 1)
return @i
end
END
return @count
goAnonymous
February 05, 2006
I'm probably looking at it too simple but here it goes :-)
DECLARE @Mentor int
SELECT @Mentor = Mentor FROM Employees WHERE EmpId=@EmpId
IF (@Mentor >= @EmpID)
BEGIN
SET @Mentor = -1
END
RETURN @Mentor
You could then check to see if the returned Mentor is -1 to return an error in the SP. Or is this not allowed either?Anonymous
February 05, 2006
There's no relationship between the integer values of the mentor IDs and the emp IDs. The IDs come from outside the system, and it's conceivable that a mentor ID might be a bigger integer than his emp ID.Anonymous
February 05, 2006
khen1234 said:
There's no relationship between the integer values of the mentor IDs and the emp IDs.
There must be. At least they must come from one source. Imagine that one of them is an IDENTITY(1,1), and another is IDENTITY(1000000000,1). In such a case original code doesn't need a buggy influention - it will not work by itself.Anonymous
February 05, 2006
CREATE FUNCTION dbo.Mentor(@EmpId int)
RETURNS int
AS BEGIN
RETURN (
SELECT COALESCE(-x.Mentor,-y.Mentor,e.Mentor) AS [Mentor]
FROM Employees e
LEFT OUTER JOIN Employees x ON x.Mentor=e.Mentor AND x.EmpID<>e.EmpID
LEFT OUTER JOIN Employees y ON y.EmpID=e.EmpID AND y.EmpID=y.Mentor
WHERE e.EmpId=@EmpId
)
END
GO
CREATE PROC ListMentors(@LastEmpIdAdded int) AS
DECLARE @i int
SET @i = @LastEmpIdAdded
WHILE (@i IS NOT NULL)
BEGIN
PRINT 'Employee: '+CAST(@i AS varchar(10))
SELECT @i = dbo.Mentor(@i)
IF @i < 0
BEGIN
RAISERROR('Mentor Error',16,1)
BREAK
END
PRINT 'Mentor: '+CAST(@i AS varchar(10))
END
GOAnonymous
February 05, 2006
What do you think about this?
CREATE FUNCTION dbo.Mentor(@EmpId int) RETURNS int
-- If return value is -1 then there should be an RAISERROR in the calling procedure
AS BEGIN
RETURN (
select case when count() = 1 then Mentor else -1 end
from Employees
where Mentor in (select Mentor from Employees where EmpId = @EmpId)
group by Mentor
-- having count() = 1 -- to stop the looping without any error
)
END
GOAnonymous
February 05, 2006
I think David won :-)Anonymous
February 06, 2006
Ennor said:
"khen1234 said:
There's no relationship between the integer values of the mentor IDs and the emp IDs.
There must be. At least they must come from one source. Imagine that one of them is an IDENTITY(1,1), and another is IDENTITY(1000000000,1)."
There isn't. Imagine a new employee being added whose ID is 8675309. It gets cached as the last emp id. Then a new employee is added. His ID happens to be 15. His mentor becomes 8675309. Thus, it's possible for a mentor ID to have a larger integer value than an employee.Anonymous
February 06, 2006
David: I like this technique, and it would work, but it amounts to changing the singleton select logic of the code to set-oriented logic; it's just that you've located this in the function rather than the proc. This violates rule #4. I've enhanced rule 4 to make this more obvious. There's actually no need to change the Mentor function, anyway.
Sarus: You're using COUNT() here, which violates rule 5.Anonymous
February 06, 2006
This is the case I'm having problems with. Can this be ruled out?
INSERT Employees VALUES (0,NULL)
INSERT Employees VALUES (1,2)
INSERT Employees VALUES (2,1)
INSERT Employees VALUES (3,0)Anonymous
February 06, 2006
Hmm. Doing this requires some type of state or pre-knowledge, which you have expressly forbidden. You have also expressly forbidden any implicit state from the IDs.
I'll be very interested to see your solution. I'm willing to bet that it violates your own rules in spirit if not the letter.Anonymous
February 06, 2006
According to your table definition, I cannot insert a Mentor ID which is larger than the EmpID.
insert employees values (101, 102)
INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint 'FK__Employees__Mento__288BBB3A'.
if I do this:
insert employees values (101, null)
insert employees values (102, null)
update employees set mentor = 102 where empid = 101
then I will have two employees (0, 102) with no mentors.Anonymous
February 06, 2006
Louie: the FK constraint just ensures that the mentor ID exists as an employee. There's no relationship between the numbers. I could add 8675309 as an employee (whose mentor is empid 9, then add empid 10, whose mentor is 8675309.)
Sjoerd: You're mistaken. Keep trying.Anonymous
February 06, 2006
I still hold that this cannot be fixed in the loop, not without changing the order of the results or some domain knowledge you are withholding.
I have a large bowl of crow here should I be proven wrong. We'll see... :-)Anonymous
February 06, 2006
And I still hold that you're mistaken :-)Anonymous
February 06, 2006
This version of the code will populate the data correctly, which is allowed with the existing constraints. David's solution doesn't work for this set of data as it walks the relationships, which can't be done if the relationship chain is broken.
INSERT Employees VALUES (0,NULL)
INSERT Employees VALUES (1,1)
INSERT Employees VALUES (2,1)
INSERT Employees VALUES (3,0)
update employees set mentor = 2 where empid = 1
I can think of ways of solving this, but they involve writing procedural versions of count(*).Anonymous
February 06, 2006
alter PROC ListMentors(@LastEmpIdAdded int)
AS
DECLARE @i int
SELECT @i = @LastEmpIdAdded -- ID of last employee added (known in advance)
PRINT 'Employee: ' + CAST(@i AS varchar(10))
SELECT @i = dbo.Mentor(@i)
PRINT 'Mentor: ' + CAST(@i AS varchar(10))
declare @MentorID int
WHILE (@i IS NOT NULL)
BEGIN
PRINT 'Employee: ' + CAST(@i AS varchar(10))
SELECT @i = dbo.Mentor(@i)
PRINT 'Mentor: ' + CAST(@i AS varchar(10))
select @MentorID = sum(mentor) from employees where mentor = @i
if @MentorID > @i
begin
raiserror('Error', 1, 1)
return @i
end
END
goAnonymous
February 06, 2006
Louie: Your sum(Mentor) is another way of counting the number of mentors which violates rule 5.Anonymous
February 06, 2006
Does the SELECT have to return a real error message or is failing instead of looping sufficient?Anonymous
February 06, 2006
Hi, if it's only the count(*) then you can do it like this ;-)
CREATE FUNCTION dbo.Mentor(@EmpId int) RETURNS int
-- If return value is -1 then there should be an RAISERROR in the calling procedure
AS BEGIN
RETURN (
select min(case when a.EmpId <> @EmpId then -1 else a.Mentor end)
from Employees a
where a.Mentor in (select Mentor from Employees where EmpId = @EmpId)
group by a.Mentor
)
END
GOAnonymous
February 07, 2006
Oh, I can do a CHEESY version:
CREATE PROC ListMentors(@LastEmpIdAdded int) AS
DECLARE @i int, @c int
SELECT @i = @LastEmpIdAdded -- ID of last employee added (known in advance)
Select @c = max(empid) from employees -- See? No count :-p
PRINT 'Employee: '+CAST(@i AS varchar(10))
SELECT @i = dbo.Mentor(@i)
PRINT 'Mentor: '+CAST(@i AS varchar(10))
WHILE (@i IS NOT NULL)
BEGIN
PRINT 'Employee: '+CAST(@i AS varchar(10))
SELECT @i = dbo.Mentor(@i)
Set @c = @c - 1
If (@c < 0) RaisError('I have detected with my mighty cheese that the chain is broken!', 16, 1) With Log
PRINT 'Mentor: '+CAST(@i AS varchar(10))
ENDAnonymous
February 07, 2006
Oh, and if Max() is too much like counting, I can always do
Select Top 1 @c = EmpID From Employees Order By EmpID DescAnonymous
February 07, 2006
Sjoerd: It's not "only COUNT(*)" -- you "can't use COUNT() or anything similar." Your last solutions also assume a numeric relationship between the emp IDs. There isn't one. What if they aren't sequential (i.e., what if there are gaps between them)? What if the max emp ID is 10000, but the others are all under 10? Neither max or top 1 is useful in those situations.Anonymous
February 07, 2006
Umm, I don't assume anything about the IDs. All I am doing is capping the number of iterations. The max EmpID is at the very least equal to the number of employees, and is therefore always a correct number of iterations to cap at. I never said efficient -- in your example, I would still do 10000 iterations, even if there are only 11 employees --, but it WILL exit the loop rather than churn away indefinitely. So there.
As for Top 1 Desc or Max being similar to Count... that's a spirit vs. letter of the law kind of thing. Hence my snarky comment in the code :-)
My bowl of crow is still waiting. And getting a bit smelly, too.Anonymous
February 07, 2006
Hi,
This is my solution attempt. Every 1000 iterations, it checks the average "vector". If the vector is exactly the same as the last check, we're in a loop. @c counter @v vector @w lastVector.
Thanks,
Louis
CREATE PROC ListMentors(@LastEmpIdAdded int) AS
DECLARE @i int, @c int, @v int, @w int
SELECT @i = @LastEmpIdAdded -- ID of last employee added (known in advance)
PRINT 'Employee: '+CAST(@i AS varchar(10))
SELECT @i = dbo.Mentor(@i)
SELECT @v = 0, @c =0
PRINT 'Mentor: '+CAST(@i AS varchar(10))
WHILE (@i IS NOT NULL)
BEGIN
PRINT 'Employee: '+CAST(@i AS varchar(10))
SELECT @i = dbo.Mentor(@i)
SELECT @v = @v +@i, @c=@c+1
PRINT 'Mentor: '+CAST(@i AS varchar(10))
-- Infinite loop check
If (@c % 1000)=0 Begin
If @v/1000 = @w/1000 Begin
Print 'Inifinite Loop - Possible table corruption detected'
Break
End
Else Begin
Select @w=@v
Select @v=0, @c=0
End
End
ENDAnonymous
February 07, 2006
As a random aside, why on Earth does the proc have the first Print/Select/Print outside of the loop to begin with? Isn't that a bit redundant?Anonymous
February 07, 2006
Sjoerd: oh, I see what you're doing. That's clever, but it won't work correctly if the max(empid) is a negative number. It's also terribly inefficient, as you've mentioned. Try to come up with a solution that makes no assumptions about the IDs themselves. They might as well be char-based emp IDs as far as this puzzle is concerned.Anonymous
February 07, 2006
The comment has been removedAnonymous
February 08, 2006
Hi,
Here is a refinement on my previous submission. The previous proc arbitrarily set the infinite loop check frequency at 1000 iterations. This won't work if the Employees table is large. e.g. we traverse to the end of 1M+ rows and we loop back. The revised proc dynamically bumps up the check frequency.
CREATE PROC ListMentors(@LastEmpIdAdded int) AS
DECLARE @i int, @c int, @v int, @w int, @iter int, @freq int
SELECT @i = @LastEmpIdAdded -- ID of last employee added (known in advance)
PRINT 'Employee: '+CAST(@i AS varchar(10))
SELECT @i = dbo.Mentor(@i)
SELECT @v = 0, @c =0, @iter=0, @freq=10
PRINT 'Mentor: '+CAST(@i AS varchar(10))
WHILE (@i IS NOT NULL)
BEGIN
PRINT 'Employee: '+CAST(@i AS varchar(10))
SELECT @i = dbo.Mentor(@i)
SELECT @v = @v +@i, @c=@c+1, @iter=@iter+1
PRINT 'Mentor: '+CAST(@i AS varchar(10))
-- Infinite loop check
If (@c % @freq)=0 Begin
If @v/@freq = @w/@freq Begin
Print 'Inifinite Loop - Possible table corruption detected'
Break
End
Else Begin
Select @w=@v
Select @v=0, @c=0
End
-- If the number of total iterations is large, bump up the check freq by power of 10
If @freq*10<@iter Begin
Select @freq=@freq*10
End
End
END
PRINT 'Total iterations: '+CAST(@iter as varchar(50))Anonymous
February 08, 2006
Louis: these are some creative solutions, but, like I said in the rules, you can't modify the Mentor function. Trust me: it isn't necessary.Anonymous
February 08, 2006
I think you've got Louis and Sarus confused :-)
I am starting to get the icky feeling the solution to this puzzle might be delayed until April 1st...Anonymous
February 08, 2006
Sjoerd: Ah, my bad. Yes -- I meant Sarus.
April 1st? Are you thinking there is no solution? At least one that doesn't violate at least the spirit of the rules I laid out?
Trust me: there's a solution, a really simple one, and it doesn't violate the rules in any way. I'm refraining from posting it until everyone has had their shot at figuring it out on their own.Anonymous
February 08, 2006
I'm saying that I do not see a solution only fixing the singleton Select, detecting both skips and loops (note that most attempts are limited to loops only). I see two possible outcomes:
- You post a simple solution within the rules. Much screaming and denting walls with my forehead will ensue.
- You post a simple solution that does not fit in the rules and/or does not work properly. Much screaming will ensue as well, albeit of a different kind :-)Anonymous
February 08, 2006
Sjoerd said: I'm saying that I do not see a solution only fixing the singleton Select, detecting both skips and loops
I've never said anything about detecting skips. The original challenge was to detect and avoid the infinite loop.
I assure you there's a solution -- a really simple one (once you know about it, I guess :-) ) that compiles with the rules in spirit as well as in actuality. It's also reasonably efficient (keep in mind that the entire proc is inefficient to start with) and requires no arcane coding constructs or anything more than a few minor changes to the proc.Anonymous
February 08, 2006
Ok, very sloppy could be optimized.
CREATE FUNCTION dbo.Mentor(@EmpId int)
RETURNS int
AS BEGIN
DECLARE @Mentor int
DECLARE @nextMentor int
DECLARE @nextMentorsMentor int
SELECT @Mentor = Mentor FROM Employees WHERE EmpId=@EmpId
SELECT @nextMentor = Mentor FROM Employees WHERE EmpId = @Mentor
SELECT @nextMentorsMentor = Mentor FROM Employees WHERE EmpId = @nextMentor
IF @EmpId = @nextMentorsMentor
SET @Mentor = -1
RETURN (@Mentor)
END
GOAnonymous
February 08, 2006
The comment has been removedAnonymous
February 09, 2006
Dis4ea: remember -- you can't modify the Mentor function...Anonymous
February 09, 2006
The comment has been removedAnonymous
February 09, 2006
this works, if you accept type conversion as an error message and the loops stop already at 8
SELECT @i = case when exists(select * from employees where empid = @i and not exists(select * from employees where empid != @i and mentor = dbo.mentor(@i))) then dbo.Mentor(@i) else 'corruption in the mentor-employee relationships detected' endAnonymous
February 09, 2006
Mattes: that's a nifty idea, but rule 4 prohibits replacing the singleton approach in the code with set-oriented logic, which is what your change amounts to. I realize a set-oriented approach would be much more efficient, but there's a particular concept I'm trying to demonstrate here. Trust me: you can fix the code without having to make any radical changes to it.Anonymous
February 09, 2006
same as Dis4ea: ... I was reading the List procedure can't be modifiedAnonymous
February 09, 2006
Using the above test case this works, although it won't be fast. There are a few more comments in the code.
CREATE PROC dbo.ListMentors(@LastEmpIdAdded int) AS
DECLARE @i int
DECLARE @previousi int
DECLARE @currentinner int
DECLARE @previousinner int
SET @i = @LastEmpIdAdded -- ID of last employee added (known in advance)
PRINT 'Employee: '+CAST(@i AS varchar(10))
SET @previousi = @i
SET @i = dbo.Mentor(@i)
PRINT 'Mentor: '+CAST(@i AS varchar(10))
WHILE (@i IS NOT NULL)
BEGIN
PRINT 'Employee: '+CAST(@i AS varchar(10))
SET @currentinner = @LastEmpIdAdded
SET @previousinner = @currentinner
WHILE (@currentinner IS NOT NULL)
BEGIN
PRINT 'Prev Employee: '+CAST(@previousinner AS varchar(10))
SET @currentinner = dbo.Mentor(@currentinner)
PRINT 'Curr Employee: '+CAST(@currentinner AS varchar(10))
IF (@i = @currentinner AND @previousi = @previousinner)
BEGIN
SET @currentinner = NULL -- exit the inner loop as this are now at the outer loop
END
IF (@i = @currentinner AND @previousi <> @previousinner) -- we got here again but from from a different employee
BEGIN
RAISERROR('Mentor Loop Error',16,1)
SET @currentinner = NULL
SET @i = NULL
END
SET @previousinner = @currentinner
END
SET @previousi = @i
SET @i = dbo.Mentor(@i)
PRINT 'Mentor: '+CAST(@i AS varchar(10))
ENDAnonymous
February 09, 2006
Ken,
I've only seen your blog today for the first time.
Good stuff!
This puzzle is not a trivial one... here's my solution (tested - works):
CREATE PROC ListMentors(@LastEmpIdAdded int) AS
DECLARE @i int
SELECT @i = @LastEmpIdAdded -- ID of last employee added (known in advance)
PRINT 'Employee: '+CAST(@i AS varchar(10))
SELECT @i = dbo.Mentor(@i)
PRINT 'Mentor: '+CAST(@i AS varchar(10))
DECLARE @j int
SELECT @j = dbo.Mentor(@i)
WHILE (@i IS NOT NULL)
BEGIN
PRINT 'Employee: '+CAST(@i AS varchar(10))
SELECT @i = dbo.Mentor(@i)
PRINT 'Mentor: '+CAST(@i AS varchar(10))
SELECT @j = dbo.Mentor(dbo.Mentor(@j))
IF (@j = @i)
BEGIN
RAISERROR('Loop Error',16,1)
SET @i = NULL
END
ENDAnonymous
February 09, 2006
Could you guys explain me the winning solution for the previous puzzle?
I can see that there's some play with addresses, but I don't know SQL Server that well.
A brief explanation would be very much appreciated :o)
ThanksAnonymous
February 09, 2006
what about that
SELECT @i = dbo.Mentor(case when not exists(select * from employees where empid != @i and mentor = dbo.mentor(@i)) then @i else 'corruption in the mentor-employee relationships detected' end)Anonymous
February 10, 2006
Ken, please put us all out of our misery :-)Anonymous
February 11, 2006
The simplest rule-compliant solution to the employee-mentor challenge I recently posted is to change...Anonymous
February 11, 2006
You are really evil :-)
But do let us guess some more please :-pAnonymous
November 11, 2006
The comment has been removedAnonymous
February 03, 2007
I guess there will be small syntax problems here and there cos I wasn't able to try it out in the system. But please confirm if this logic will work.......... CREATE PROC ListMentors(@LastEmpIdAdded int) AS DECLARE @empId int, @mentorID int, @CheckForReptitiveMentor int, @bitFirstTimeLoop bit SET @CheckForReptitiveMentor = 0 SELECT @empId = @LastEmpIdAdded -- ID of last employee added (known in advance) PRINT 'Employee: '+CAST(@empId AS varchar(10)) SELECT @mentorID = dbo.Mentor(@empId ) PRINT 'Mentor: '+CAST(@mentorID AS varchar(10)) WHILE (@mentorID IS NOT NULL) BEGIN SELECT @empId = @mentorID PRINT 'Employee: '+CAST(@empId AS varchar(10)) SELECT @mentorID = dbo.Mentor(@empId) If @MentorId= @CheckForReptitiveMentor BEGIN print "Corrupt data" @mentorID = null END PRINT 'Mentor: '+CAST(@mentorID AS varchar(10)) if @bitFirstTimeLoop = 0 BEGIN set @bitFirstTimeLoop = 1 SELECT @CheckForReptitiveMentor = @MentorId END ENDAnonymous
June 27, 2007
Hi - I am too late replying but can this be a possible solution ( i havent looked at the answer) CREATE PROC ListMentors(@LastEmpIdAdded int) AS DECLARE @i int , @x int , @y int , @a int , @b int SELECT @i = @LastEmpIdAdded -- ID of last employee added (known in advance) PRINT 'Employee: '+CAST(@i AS varchar(10)) SELECT @i = dbo.Mentor(@i) PRINT 'Mentor: '+CAST(@i AS varchar(10)) WHILE (@i IS NOT NULL) BEGIN PRINT 'Employee: '+CAST(@i AS varchar(10)) select @x = empid , @y = mentor from employees where empid = @i SELECT @i = dbo.Mentor(@i) select @a = empid , @b = mentor from employees where empid = @i if exists (select 1 from employees where empid < @y and mentor= @y) OR ( @x = @b and @y = @a ) OR (@x=@y) -- cyclic error detected begin select 'Cyclic error detected , aborting proc' return end PRINT 'Mentor: '+CAST(@i AS varchar(10)) ENDAnonymous
May 30, 2008
Today’s entry is another T-SQL puzzle. Steve Kass took the prize for the best solution to my last T-SQL puzzle , and several others came up with some pretty original solutions of their own. I especially liked the ones that were T-SQL specific – ones thaAnonymous
June 04, 2008
Today’s entry is another T-SQL puzzle. Steve Kass took the prize for the best solution to my last T-SQL puzzle , and several others came up with some pretty original solutions of their own. I especially liked the ones that were T-SQL specific – ones thaAnonymous
June 13, 2009
PingBack from http://firepitidea.info/story.php?id=916Anonymous
June 16, 2009
PingBack from http://workfromhomecareer.info/story.php?id=10700Anonymous
June 16, 2009
PingBack from http://topalternativedating.info/story.php?id=1246Anonymous
June 19, 2009
PingBack from http://edebtsettlementprogram.info/story.php?id=24816