SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Join to Hierarchical query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tkstock
Starting Member

USA
2 Posts

Posted - 09/21/2012 :  14:17:24  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/21/2012 :  14:23:57  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 09/21/2012 :  14:25:04  Show Profile  Reply with Quote
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

USA
328 Posts

Posted - 09/24/2012 :  16:00:36  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 09/24/2012 :  16:44:20  Show Profile  Reply with Quote
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

USA
2 Posts

Posted - 09/25/2012 :  08:09:03  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 09/25/2012 :  12:41:53  Show Profile  Reply with Quote
cool

welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000