Stored Procedures and SQL Server

I am trying to write a query that allows me to check whether a particular node is within a hierarchy of nodes...all with a parent/child relationship.I know in Oracle, there's "connect by prior", which works out, but in SQL Server, I think I have to do a Stored Procedure. I developed one that returns all children (and children's children, etc.) nodes given an input node.Is it possible to include the results from that stored procedure within another query...something like:LiveReport SQL:SELECT * FROM dtree where dataid in (sp_getChildren 12345)If stored procedures can't be called from within dynamic sql, what options are there?Thanks in advance!

Find more posts tagged with

Comments

volvostephen

Message from via eLinkTo get all children and then see if that node is in the result set seems tobe far more then you wanted based on your first statement (just from aperformance perspective this could be very slow). Given a node, it is easyto recurse up the tree. You could take this SQL Server Custom functionbelow and have it return a Boolean rather then a Full Path - it is the samekind of thing you want I think.If I have made incorrect assumptions let me know - I love getting creativewith SQL Server.CREATE FUNCTION [livelink].[GetFullPath] (@DataID INT) RETURNS varChar(8000) AS BEGIN Declare @TmpParentID intDeclare @FullPath varChar(8000)Declare @Name varChar(255)Declare @TmpName varChar(255)if @dataID <> 0Begin --build up a list of parentID's for the given dataID while @DataID <> -1 Begin Select @TmpParentID = ParentID from dtree where dataID =@rowcount = 0 BREAK --this logic is for parentID's that are negative if @dataID < -1 and @TmpParentID = -1 begin --we got -1 as the parentID but the dataID isnegative so try again with the (+) of the dataID Set @DataID = ABS(@DataID) end else begin Set @DataID = @TmpParentID end Set @TmpName = @Name Select @Name=Name from dtree with (nolock) where dataID =@DataID if @FullPath is NULL Begin Set @FullPath = @Name end else begin if @TMPName <> @Name begin Set @FullPath = @Name + ':' + @FullPath end end endendRETURN @FullPathEND-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: August 26, 2004 4:24 PMTo: eLink RecipientSubject: Stored Procedures and SQL ServerStored Procedures and SQL ServerPosted by Drane, Matthew on 08/26/2004 04:15 PMI am trying to write a query that allows me to check whether a particularnode is within a hierarchy of nodes...all with a parent/child relationship.I know in Oracle, there's "connect by prior", which works out, but in SQLServer, I think I have to do a Stored Procedure. I developed one thatreturns all children (and children's children, etc.) nodes given an inputnode.Is it possible to include the results from that stored procedure withinanother query...something like:LiveReport SQL:SELECT * FROM dtree where dataid in (sp_getChildren 12345)If stored procedures can't be called from within dynamic sql, what optionsare there?Thanks in advance![To reply to this thread, use your normal E-mail reply function.]============================================================Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe