November 25, 2005 at 7:19 am
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??
--***************************************************************************
--* 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
November 25, 2005 at 3:04 pm
Use the trace to see if the proc is called recursivly
Anyway from what I can see here your code is a little wrong
NOTICE that @tblMenuID is a VARIABLE wich is empty again at the second call of your SP
and @menuID won't be NULL so in this SP you DON'T HAVE ANYTHING in this table VARIABLE
so your cursor won't have any rows to fech ....
////////////////////////////////////////////////////////////////////////////////////////////
--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
///////////////////////////////////////////////////////////////////////////////////////////
hope this helps you : ))
Vasc
November 28, 2005 at 9:07 am
i modified my procedure to do it without recursion as follows.But now how do i set the level,index numbers?
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???
--This variable will hold the parent record ID who's children we want to find.
declare @RecordID int
set @RecordID = 69
--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 Menu_ID
from tblMenu
where Menu_ID = @RecordID
--Add new child records until exhausted.
while @@RowCount > 0
insert into @RecordList (RecordID)
select Menu_ID
from tblMenu
inner join @RecordList RecordList on tblMenu.ParentMenu_ID = RecordList.RecordID
where not exists (select * from @RecordList CurrentRecords where CurrentRecords.RecordID = tblMenu.Menu_ID)
--Return the result set
select RecordID
from @RecordList
November 29, 2005 at 12:27 am
I did the level number.
But the indexing is still a problem cause it depends on index of parent in case it's a first child and depends on index of previous child if it is more than first child.
any ideas???
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply