Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Join to Hierarchical query

Author  Topic 

tkstock
Starting Member

2 Posts

Posted - 2012-09-21 : 14:17:24
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!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-21 : 14:23:57
Two ways I can think of doing what you are trying to do:

1. Use "INSERT..EXEC". You would first create a temp table with exactly the same columns as the output of the stored procedure. Then, run the following:

INSERT INTO #TempTable
EXEC [dbo].[udp_GetHeirarchyForUserID] @UserId = 12345;
Then you can join to the temp table.

2. Create a new stored procedure that uses similar logic to what you have now, except, it implements the logic you described including the join on the new table.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-21 : 14:25:04
you cant directly use stored procedure in a join

the available options are

1. Populate temporary table with procedure result and join it with other tables
2. Include all logic in same procedure itself
3. Convert the above procedure as a table valued UDF and use it in join with table
4. Not at all recommended approach, but you could do this as well


http://beyondrelational.com/modules/2/blogs/70/posts/10812/select-columns-from-exec-procedurename-is-this-possible.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2012-09-24 : 16:00:36
quote:
Originally posted by visakh16

you cant directly use stored procedure in a join

the available options are

1. Populate temporary table with procedure result and join it with other tables
2. Include all logic in same procedure itself
3. Convert the above procedure as a table valued UDF and use it in join with table
4. Not at all recommended approach, but you could do this as well


http://beyondrelational.com/modules/2/blogs/70/posts/10812/select-columns-from-exec-procedurename-is-this-possible.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





Be careful with #3 -- UDFs have some known performance issues in SQL Server. Some real quick digging found this, but there are better sources out there that reiterate the results:

http://allinthehead.com/retro/247/user-defined-functions-considered-harmful
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-24 : 16:44:20
quote:
Originally posted by lazerath

quote:
Originally posted by visakh16

you cant directly use stored procedure in a join

the available options are

1. Populate temporary table with procedure result and join it with other tables
2. Include all logic in same procedure itself
3. Convert the above procedure as a table valued UDF and use it in join with table
4. Not at all recommended approach, but you could do this as well


http://beyondrelational.com/modules/2/blogs/70/posts/10812/select-columns-from-exec-procedurename-is-this-possible.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





Be careful with #3 -- UDFs have some known performance issues in SQL Server. Some real quick digging found this, but there are better sources out there that reiterate the results:

http://allinthehead.com/retro/247/user-defined-functions-considered-harmful


I know that
I was not advocating for it
UDFs will be only of least preffered options. just specified it as its one of way in which you can get requirement done

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

tkstock
Starting Member

2 Posts

Posted - 2012-09-25 : 08:09:03
Thanks for the replies.

I went with the #TempTable solution - which appears to work the best for the situation. I don't do enough SQL at this point to be "used" to using temp tables! :)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-25 : 12:41:53
cool

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -