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]
@UserID varchar(10)
AS
BEGIN
WITH Tree ([LoginID]
,[GroupID]
,[SupervisorID]
,Level)
AS
(
-- Anchor member definition
SELECT [LoginID]
,[GroupID]
,[SupervisorID]
,0 as Level
FROM [tReporting_Structure]
where SupervisorID = @UserID
UNION ALL
-- Recursive member definition
SELECT rs.[LoginID]
,rs.[GroupID]
,rs.[SupervisorID]
,Level + 1
FROM [tReporting_Structure] as rs
INNER JOIN Tree AS d
ON rs.SupervisorID = d.LoginID
)
-- Statement that executes the CTE
SELECT [LoginID]
,un.LastName
,un.FirstName
,d.[GroupID]
,[SupervisorID]
,g.GroupName
,Level
FROM Tree d
left join tReporting_Group g
on d.GroupID = g.GroupID
left join tUserNames un
on d.LoginID = un.UserID;
END
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!
Join to Hierarchical query
Posted - 09/21/2012 : 14:17:24
