Iterative Procedure - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Iterative Procedure

Author

Topic

Shilpa22
Starting Member

37 Posts

Posted - 2010-10-06 : 09:01:38

Hello Frnds,
I have a TreeView with CheckBoxes. Every Node is represented by FolderID. For ex. SuperNode(FolderId=100) is the Root Folder and ChildNode1 and ChildNode2 are the subfolders(ParentId of these Folders are 100 and Parent Id of ChildNode2 is 101) .and subfolder can contain sub folders(to which parent Id will be 101).
In the Table1. I have columns FolderId,ParentId,Name,Desc.
So in the TreeView when I select ParentNode(100). I shud get that info and all the subfolders Name,Desc details.
I need a sql server proc for this. Please help me in this.

Thanks in anticipation,
Silpa

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-06 : 10:44:12

Here is an old sample I had saved off. It includes code to avoid circular references as well. You should be able to adapt it to your table as it is based on hierarchal structure of id/parentid:

EDIT:
Recursive CTE is a good way to handle parent/child solutions but avoiding circular references was a problem (for me anyway). I'm pretty sure this solution was inspired by PESO. It may even be his code I don't remember :)


DECLARE	@Sample TABLE
	(
		PageID INT,
		ParentID INT
	)

INSERT	@Sample
SELECT	46, 18 UNION ALL
SELECT	 5, 46 UNION ALL
SELECT	 6,  5 UNION ALL
SELECT	 7,  6 UNION ALL

select 46, 7 union all --circular reference

SELECT	 8,  6 UNION ALL
SELECT	 9,  6 UNION ALL
SELECT	10,  6 UNION ALL
SELECT	11,  6 UNION ALL
SELECT	12,  6 UNION ALL
SELECT	13,  6 UNION ALL
SELECT	14,  6 

;WITH Yak(PageID, parentid, p, [pi])
AS (
	SELECT	s.PageID, 0, replace(convert(varchar(max), str(s.pageid,3)),' ','0'), convert(bigint,0)
	FROM	@Sample s
	left outer join @sample x on x.pageid = s.parentid
       where x.pageid is null

	UNION ALL

	SELECT		s.PageID, y.pageid, replace(y.p + '-' + str(s.pageid,3),' ','0'), patindex( '%' + replace(str(s.pageid,3),' ','0') + '%', y.p)
	FROM		Yak AS y
	INNER JOIN	@Sample AS s ON s.ParentID = y.PageID

       where   patindex( '%' + replace(str(s.pageid,3),' ','0') + '%', y.p) = 0
)

SELECT	PageID, parentid, p as [structure] --, [pi]
FROM	Yak
option (maxrecursion 10)

OUTPUT:
PageID      parentid    structure
----------- ----------- ------------------
46          0           046
5           46          046-005
6           5           046-005-006
7           6           046-005-006-007
8           6           046-005-006-008
9           6           046-005-006-009
10          6           046-005-006-010
11          6           046-005-006-011
12          6           046-005-006-012
13          6           046-005-006-013
14          6           046-005-006-014

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources