recursive procedure help - 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 2000 Forums

SQL Server Development (2000)

recursive procedure help

Author

Topic

mariechristine
Starting Member

27 Posts

Posted - 2005-11-25 : 09:24:00

Please i need help in my recursive procedure to get all menus and their submenus.

When i call the procedure again, it isn't entering

I don't know why???? Anything wrong in my logic??
When it calls itself again, the cursor continues from fetching from my table?
or it fetches wrongly??

--***************************************************************************
--* TYPE: STORED PROCEDURE (PUBLIC)
--* NAME: GETmenuCursRENmenuByUser
--* AUTHOR: CARINE MAALOUF
--* DESC: PROCEDURE THAT RETURN ALL THE menuCursREN AND SUBmenuCursREN OF A SPECIFIC menu
--* PARAMETERS DESCRIPTION
--* ---------------------------------------------------------------------------
--* @GROUPID INPUT INTEGER THAT CONTAINS THE ID OF THE GROUP TO GET ITS menu menuCursREN
--* @LANGUAGEID INPUT INTEGER THAT CONTAINS THE ID OF THE LANGUAGE
--* @PARENTID INPUT INTEGER THAT CONTAINS THE ID OF THE menu TO GET ITS menuCursREN
--* @POSTLEVEL INPUT INTEGER THAT CONTAINS THE LEVEL OF THE ACCOUNT
--**************************************************************************
CREATE PROCEDURE DBO.GetChildrenMenuByUserGroup(@GROUPID INT, @languageID INT,
@menuID bigint,@parentID BIGINT,
@COMMUNITYid INT
AS

SET NOCOUNT ON
/***********************************/
/*Declare variables for final result**/
DECLARE @menuIMAGE NVARCHAR(255), @menuTITLE NVARCHAR(255), @menuDESCRIPTION NVARCHAR(255)
DECLARE @menuITEMAPPURL NVARCHAR(255)

/***Get in table @tblMenuID all menus belonging to group id @groupID*/
Declare @tblMenuID table (MenuID int, ParentMenuID int, MenuImage NVARCHAR(255),
MenuItemAppURL NVARCHAR(255),MenuTitle NVARCHAR(255),
MenuDescription NVARCHAR(255) )
/******************************************************************************/

--IF FIRST TIME
IF @menuID is null
begin

Insert into @tblMenuID
select M.Menu_ID , M.ParentMenu_Id, M.menu_Image,MI.menuItem_AppURL,
ML.menu_Title,ML.menu_Description
from tblMenuGroups MG inner join tblMenu M on MG.Menu_ID=M.menu_ID
inner join tblMenuItems Mi on Mi.Menu_ID = M.menu_ID
left outer join tblMenuLangdesc ML on ML.menu_Id = M.Menu_ID
where MG.Group_ID = @groupID
and MI.Community_ID =@COMMUNITYid
and ML.Language_ID = @languageID
order by M.Menu_ID

END --end if first time this procedure executes

/**Set cursor for @tblMenuIDS*******/
DECLARE menuCurs CURSOR LOCAL FOR
select * from @tblMenuID
order by MenuID
/**********************************/

/*******Process for each menu id**************************/
OPEN menuCurs
--SELECT @nbrROWSmenuCurs = @@CURSOR_ROWS
FETCH NEXT FROM menuCurs INTO @menuID,@ParentID, @menuIMAGE, @menuTITLE, @menuDESCRIPTION,
@menuITEMAPPURL
SET @NEXTELEMENT = @MENUELEMENT

WHILE (@@FETCH_STATUS = 0)
BEGIN

/**IF this menu is a subparent of another parent i.e its parent id not null *****************/
if @parentID is not Null
BEGIN

--insert values of sub level menu
INSERT #NESTEDCHILDByUserGroup (menuID,menuPARENTID,menuIMAGE,menuTITLE,menuDESCRIPTION,
menuITEMAPPURL)
VALUES (@menuID,@ParentID,@menuIMAGE, @menuTITLE, @menuDESCRIPTION,
@menuITEMAPPURL )

--select parent of the sub level menu
select @menuID=M.menu_ID,@parentID=M.PARENTmenu_ID,@menuIMAGE=M.menu_IMAGE,
@menuTITLE=ML.menu_TITLE, @menuDESCRIPTION=ML.menu_DESCRIPTION,
@menuITEMAPPURL=MI.menuITEM_APPURL
FROM TBLmenu M
LEFT OUTER JOIN TBLmenuLANGDESC ML ON M.menu_ID = ML.menu_ID
left JOIN TBLmenuITEMS MI ON M.menu_ID = MI.menu_ID
WHERE M.menu_ID = @parentID
AND ML.LANGUAGE_ID = @LANGUAGEID

END

/**IF this is the first level parent*****************/
ELse IF @PARENTID IS NULL
BEGIN

--insert its values
INSERT #NESTEDCHILDByUserGroup (menuID,menuPARENTID,menuIMAGE,menuTITLE,menuDESCRIPTION,
menuITEMAPPURL)
VALUES (@menuID,@ParentID,@menuIMAGE, @menuTITLE, @menuDESCRIPTION,
@menuITEMAPPURL )

END

IF @@NESTLEVEL <= 32
Begin

EXEC GetChildrenMenuByUserGroup @GROUPID , @languageID , @menuID ,@parentID, @communityID

End

FETCH NEXT FROM menuCurs INTO @menuID,@ParentID,@menuIMAGE, @menuTITLE, @menuDESCRIPTION,
@menuITEMAPPURL
END

CLOSE menuCurs
DEALLOCATE menuCurs
SET NOCOUNT OFF

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-25 : 09:26:09

Do you want to retreive tree structures?

Refer these
http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm
http://www.nigelrivett.net/RetrieveTreeHierarchy.html
http://www.seventhnight.com/treestructs.asp

Madhivanan

Failing to plan is Planning to fail

mariechristine
Starting Member

27 Posts

Posted - 2005-11-25 : 10:04:21

the above mentioned links talk about walking the tree top-down from parent to child.
are there any examples of bottom-up walk (child to parent)?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-25 : 11:04:31

I strongly discourage you from using recursive procedures, which are not only inefficient, but are also hampered by a 32 level recursion limit.
The link to nigel rivet's site posted above lists a non-recursive technique. Here is mine, which again shows an example of a top-down search. The code is simple enough that you should easily be able to reverse the parent/child field references to perform a top-up search.

--This variable will hold the parent record ID who's children we want to find.
declare @RecordID int
set @RecordID = 13

--This table will accumulate our output set.
declare @RecordList table (RecordID int)

--Seed the table with the @RecordID value, assuming it exists in the database.
insert into @RecordList (RecordID)
select RecordID
from YourTable
where YourTable.RecordID = @RecordID

--Add new child records until exhausted.
while @@RowCount > 0
insert into @RecordList (RecordID)
select YourTable.RecordID
from YourTable
    inner join @RecordList RecordList on YourTable.ParentID = RecordList.RecordID
where not exists (select * from @RecordList CurrentRecords where CurrentRecords.RecordID =  YourTable.RecordID)

--Return the result set
select RecordID
from @RecordList

mariechristine
Starting Member

27 Posts

Posted - 2005-11-28 : 10:18:39

iT WORKDED without recursion.

Now, i need to number the levels and indices of parent and children
ex:
parent 1 : level= 1, index=1
child of parent 1(C1) : level=2, index=2
another child of P1(C2): level=2, index=3

child of C2: level = 3, index = 4
child of P1(c3): level =2, index = 5

How DO I Do that???

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-28 : 11:28:55

Create an int variable and initialize it to 0.
Then add a LEVEL INT column to your temp table.
Insert the int variable into the LEVEL column along with the RecordID, and increment the variable each loop.

Post your code if you need more help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources