CTE - Determine Closest Item

I have included the create table statements, sample data and a stored procedure.

I have a stored procedure that walks the associations so that I can assemble all the nodes of the tree. I think this is working properly.

I have two requests/needs/problems that I need to solve:
(1)
I want to create a NEW stored procedure that takes a WhoID and an AssocID.
This new stored procedure would show the shortest or closest path between WhoID and AssocID.

For example, if I pass in 6,1 to this new proc I want the stored procedure to return the hierarchy with the closest path between them. Based on the sample data below, the path would be
6 - 8
8 - 2
2 - 3
3 - 1

(2)
I might also need a proc that accepts two inputs (again, lets use 6,1) but would show all paths / branches of the tree where person 1 exists. Based on the stored procedure procCTE_Assoc 6, this solution would exclude the "fred and jeff" node but would include the other two nodes because person 1 is within the other two nodes.


Please provide your suggestions, comments, or solutions!





CREATE TABLE dbo.Who(
WhoID bigint IDENTITY(1,1) NOT NULL,
FName varchar(50) NOT NULL
)
GO
CREATE TABLE dbo.Assoc(
WhoID bigint NOT NULL,
AssocID bigint NOT NULL
) ON PRIMARY



SET IDENTITY_INSERT dbo.Who ON
GO
INSERT INTO Who (WhoID,FName) VALUES (1,'user1')
INSERT INTO Who (WhoID,FName) VALUES (2,'user2')
INSERT INTO Who (WhoID,FName) VALUES (3,'admin1')
INSERT INTO Who (WhoID,FName) VALUES (4,'admin2')
INSERT INTO Who (WhoID,FName) VALUES (5,'admin3')
INSERT INTO Who (WhoID,FName) VALUES (6,'Keith')
INSERT INTO Who (WhoID,FName) VALUES (7,'fred')
INSERT INTO Who (WhoID,FName) VALUES (8,'Joe')
INSERT INTO Who (WhoID,FName) VALUES (9,'Jack')
INSERT INTO Who (WhoID,FName) VALUES (10,'Doug')
INSERT INTO Who (WhoID,FName) VALUES (11,'Mark')
INSERT INTO Who (WhoID,FName) VALUES (12,'Al')
INSERT INTO Who (WhoID,FName) VALUES (13,'Jeff')
INSERT INTO Who (WhoID,FName) VALUES (14,'Brandon')
INSERT INTO Who (WhoID,FName) VALUES (15,'Ashley')
INSERT INTO Who (WhoID,FName) VALUES (16,'Lisa')
INSERT INTO Who (WhoID,FName) VALUES (17,'Julie')
INSERT INTO Who (WhoID,FName) VALUES (18,'Brian')
go
SET IDENTITY_INSERT dbo.Who OFF
GO
INSERT INTO Assoc (WhoID,AssocID) VALUES (1,4)
INSERT INTO Assoc (WhoID,AssocID) VALUES (2,1)
INSERT INTO Assoc (WhoID,AssocID) VALUES (2,3)
INSERT INTO Assoc (WhoID,AssocID) VALUES (6,7)
INSERT INTO Assoc (WhoID,AssocID) VALUES (6,8)
INSERT INTO Assoc (WhoID,AssocID) VALUES (7,13)
INSERT INTO Assoc (WhoID,AssocID) VALUES (8,2)
INSERT INTO Assoc (WhoID,AssocID) VALUES (14,8)
INSERT INTO Assoc (WhoID,AssocID) VALUES (6,15)
INSERT INTO Assoc (WhoID,AssocID) VALUES (15,18)
INSERT INTO Assoc (WhoID,AssocID) VALUES (15,17)
INSERT INTO Assoc (WhoID,AssocID) VALUES (17,5)
INSERT INTO Assoc (WhoID,AssocID) VALUES (5,1)



go
create proc dbo.procCTE_Assoc
@WhoID bigint
AS

WITH GroupsCTE (WhoID, AssocID, FName, FNameAssoc, Lvl, Sort, GroupWithIndent)
AS
(
-- Anchor Who definition
SELECT M.WhoID, MA.AssocID, M.FName, AssocWho.FName, 0 AS Lvl,
CONVERT(varchar(2000),AssocWho.FName) AS Sort,
CONVERT(varchar(255), AssocWho.FName)
FROM Who M
INNER JOIN Assoc MA ON M.WhoID = MA.WhoID
INNER JOIN Who AssocWho ON AssocWho.WhoID = MA.AssocID
WHERE M.WhoID = @WhoID
UNION ALL
-- Recursive Who definition
SELECT g1.WhoID, MA1.AssocID, g1.FName, g2.FName, Lvl + 1,
CONVERT(varchar(2000),RTRIM(Sort) + '|' + g2.FName),
CONVERT(varchar(255), REPLICATE ('| ' , Lvl+1) + g2.FName)
FROM Who g1
INNER JOIN Assoc MA1 ON g1.WhoID = MA1.WhoID
INNER JOIN Who g2 ON g2.WhoID = MA1.AssocID
INNER JOIN GroupsCTE CTE
ON MA1.WhoID = CTE.AssocID
)

SELECT *
FROM GroupsCTE
ORDER BY Sort

RETURN (0)
GO

exec procCTE_Assoc 6