I have a hierarchical query (which works) in a stored procedure as follows which represents people within a hierarchical organization:
ALTER PROCEDURE [dbo].[udp_GetHeirarchyForUserID]
WITH Tree ([LoginID]
-- Anchor member definition
,0 as Level
where SupervisorID = @UserID
-- Recursive member definition
,Level + 1
FROM [tReporting_Structure] as rs
INNER JOIN Tree AS d
ON rs.SupervisorID = d.LoginID
-- Statement that executes the CTE
FROM Tree d
left join tReporting_Group g
on d.GroupID = g.GroupID
left join tUserNames un
on d.LoginID = un.UserID;
I need to be able to join the output of this procedure with another table containing UserIDs as one of their elements... such that I can pull records for a given Supervisor (which would give me the records for everyone who works underneath them).
How can I join to the result of a stored procedure? Am I even going about this the right way?
Any help would be appreciated! Thanks!