How about doing this entire thing (traversing the heirarchy) in stored procedure itself...!!! chek it out...It surely works.. sp....begins.. Declare @Family Table(Cat_ID int) Declare @Stack Table(Item int, Lvl smallInt) Declare @level int Declare @current int SELECT @Current = @NodeID (@NodeId is the parameter u pass to get children of this object) SELECT @level = 1
INSERT INTO @stack VALUES (@current, @level) WHILE @level > 0 BEGIN IF EXISTS (SELECT Item FROM @stack WHERE Lvl = @level) BEGIN SELECT @current = Item FROM @stack WHERE Lvl = @level INSERT INTO @Family(Cat_ID) VALUES (@Current) DELETE FROM @stack WHERE Lvl = @level AND item = @current INSERT @stack SELECT Cat_ID, @level + 1 FROM PRD_CATEGORY_MASTER WHERE Cat_Parent = @current IF @@ROWCOUNT > 0 --IF there are any children... SELECT @level = @level + 1 END ELSE SELECT @level = @level - 1 END -- WHILE select * from @Family @Family will give u all the children of the specified node. Here PRD_CATEGORY_MASTER is the table which resembles like the one which u have given. It has Cat_ID Name Cat_Parent.. OK...enjoy.. Praveen P. [Non-text portions of this message have been removed] ------------------------ Yahoo! Groups Sponsor --------------------~--> Get Bzzzy! (real tools to help you find a job). Welcome to the Sweet Life. http://us.click.yahoo.com/A77XvD/vlQLAA/TtwFAA/saFolB/TM --------------------------------------------------------------------~-> Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
