Expanding Hierachies newbie help please
-
Hi,
Following on from my post about dealing with a hierachical data structure I am still trying to make a stored procure to output all the records within a particular data structure.
I have taken the sproc listed in the BOL for 'Expanding Hierarchies' and have been modifying it to work with my tables, but am lost as to how to output the records that I want.
This is my first sproc so I guess a steep learning curve is ahead!
Here is the code so far:
ALTER PROCEDURE dbo.procSiteObjects_GetBySiteID
(
@SiteObjectID INT
)
AS
SET NOCOUNT ON
DECLARE @level int, @line char(20)
CREATE TABLE #stack (SiteObjID int, level INT, SiteObjectName nvarchar(100), ParentID INT, SortOrder int)
INSERT INTO #stack VALUES (@SiteObjectID, 1,'',@SiteObjectID,0)
SELECT @level = 1
WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @level)
BEGIN
SELECT @SiteObjectID = SiteObjID
FROM #stack
WHERE level = @LEVEL
ORDER BY SortOrder, SiteObjectName
SELECT @line = space(@level - 1) + @SiteObjectID
PRINT @line
DELETE FROM #stack
WHERE level = @level
AND SiteObjID = @SiteObjectID
INSERT #stack
/* SELECT child, @level + 1
FROM hierarchy
WHERE parent = @SiteObjectID*/
SELECT DISTINCT TOP 100 PERCENT so.SiteObjectID,
@LEVEL + 1,
so.SiteObjectName,
sol.ParentID,
sol.SortOrder
FROM dbo.SiteObjects AS so LEFT
OUTER JOIN dbo.Sites AS s ON so.SiteObjectID = s.SiteRootID LEFT
OUTER JOIN dbo.SiteObjectLinks AS sol ON so.SiteObjectID = sol.SiteObjectID LEFT
OUTER JOIN dbo.SiteObjectLinks AS SiteObjectLinks ON so.SiteObjectID = SiteObjectLinks.ParentID
WHERE (
sol.ParentID = @SiteObjectID
)
ORDER BY sol.SortOrder,
so.SiteObjectName
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END -- WHILE
At the moment the sproc is just printing out the SiteObjectID, what I actually need it to do is to return a recordset that I can then pickup in my .Net code.
Any pointers would be appreciated.
Cheers,
Julian
-
There is an article on home page of this site.
_____________
Code for TallyGenerator -
Which article are you referring to?
-
Search this site (SQL Server Central) for hierarchies - you will find several detailed articles - also make sure you read the discussion threads with the articles..
Good lucK!
-
Thanks, I'll check it out.
I've actually managed to get my original code working, but it will need some tweaking at some point so will do more research then.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply