Menu Hierarchy Display Order (Recursion)

Hi -
I'm using the following Table and Stored Procedure below, to store and
access a menu hierarchy. The recursive stored procedure is working
perfectly and returns a text string outlining my current menu hierarchy.
Currently the stored procedure returns the hiearchy in the order that the
records were created (MenuItemID)
However, I've recently added a "DisplayOrder" column to my table. For
example, let's say that I want Biking to appear before Skiing. Changing the
item's MenuItemID is not an option. So, I'm using a DisplayOrder column. I
need to integrate the DisplayOrder column into my stored procedure so that
it returns the hierarchy in the correct order. I'm not sure what I'm doing
wrong, but I just can't seem to get this part working.
Any help would be appreciated.
Thanks,
Brian
CREATE TABLE MenuItem
(
MenuItemID INT,
ItemName VARCHAR(30),
ParentID INT,
DisplayOrder INT
)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(1, 'Activities', NULL, 0)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(2, 'Fishing', 1, 0)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(3, 'Skiing', 1, 1)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(4, 'Hiking', 1, 2)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(5, 'Biking', 1, 3)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(6, 'Swimming', 1, 4)
CREATE PROCEDURE spCreateMenu (
@.TargetMenuItemID INT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.MenuItemID INT,
@.ItemName VARCHAR(30),
@.ParentID INT,
@.DisplayOrder INT
SELECT @.MenuItemID=MenuItemID, @.ItemName=ItemName, @.ParentID=ParentID FROM
MenuItem WHERE MenuItemID = @.TargetMenuItemID
PRINT REPLICATE('-', @.@.NESTLEVEL * 4) + @.ItemName
SET @.MenuItemID = (SELECT MIN(MenuItemID) FROM MenuItem WHERE ParentID =
@.TargetMenuItemID)
WHILE @.MenuItemID IS NOT NULL
BEGIN
EXEC spCreateMenu @.MenuItemID
SET @.MenuItemID = (SELECT MIN(MenuItemID) FROM MenuItem WHERE ParentID =
@.TargetMenuItemID AND MenuItemID > @.MenuItemID)
END
END
EXEC spCreateMenu 1Hi Brian,
Try this.. Hope this helps.
ALTER PROCEDURE spCreateMenu (
@.TargetMenuItemID INT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.MenuItemID INT,
@.ItemName VARCHAR(30),
@.ParentID INT,
@.DisplayOrder INT,
@.rowcount int
SELECT @.ItemName=ItemName, @.ParentID=ParentID FROM
MenuItem WHERE MenuItemID = @.TargetMenuItemID
PRINT REPLICATE('-', @.@.NESTLEVEL * 4) + @.ItemName
SELECT TOP 1 @.MenuItemID = ISNULL(MenuItemID,0), @.DisplayOrder =
DisplayOrder FROM MenuItem WHERE ParentID =
@.TargetMenuItemID ORDER BY DISPLAYORDER
set @.rowcount = @.@.rowcount
WHILE (@.rowcount <> 0)
BEGIN
EXEC spCreateMenu @.MenuItemID
SELECT TOP 1 @.MenuItemID = ISNULL(MenuItemID,0), @.DisplayOrder =
DisplayOrder FROM MenuItem WHERE ParentID =
@.TargetMenuItemID AND DisplayOrder> @.DisplayOrder ORDER BY DISPLAYORDER
set @.rowcount = @.@.rowcount
END
END|||Excellent - Thanks very much for your help!!
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:B9AE2855-8675-4E07-BF3D-52A2BF892335@.microsoft.com...
> Hi Brian,
> Try this.. Hope this helps.
> ALTER PROCEDURE spCreateMenu (
> @.TargetMenuItemID INT
> )
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.MenuItemID INT,
> @.ItemName VARCHAR(30),
> @.ParentID INT,
> @.DisplayOrder INT,
> @.rowcount int
> SELECT @.ItemName=ItemName, @.ParentID=ParentID FROM
> MenuItem WHERE MenuItemID = @.TargetMenuItemID
> PRINT REPLICATE('-', @.@.NESTLEVEL * 4) + @.ItemName
> SELECT TOP 1 @.MenuItemID = ISNULL(MenuItemID,0), @.DisplayOrder =
> DisplayOrder FROM MenuItem WHERE ParentID =
> @.TargetMenuItemID ORDER BY DISPLAYORDER
> set @.rowcount = @.@.rowcount
> WHILE (@.rowcount <> 0)
> BEGIN
> EXEC spCreateMenu @.MenuItemID
> SELECT TOP 1 @.MenuItemID = ISNULL(MenuItemID,0), @.DisplayOrder =
> DisplayOrder FROM MenuItem WHERE ParentID =
> @.TargetMenuItemID AND DisplayOrder> @.DisplayOrder ORDER BY DISPLAYORDER
> set @.rowcount = @.@.rowcount
> END
> END|||Look up CTEs (common table expressions). They handle recursive logic
without actually having a recursive stored procedure. One SQL statement
will do it for you.
"Brian Patrick" <bpatrick100@.hotmail.com> wrote in message
news:OUFoLJKaGHA.3652@.TK2MSFTNGP03.phx.gbl...
> Hi -
> I'm using the following Table and Stored Procedure below, to store and
> access a menu hierarchy. The recursive stored procedure is working
> perfectly and returns a text string outlining my current menu hierarchy.
> Currently the stored procedure returns the hiearchy in the order that the
> records were created (MenuItemID)
> However, I've recently added a "DisplayOrder" column to my table. For
> example, let's say that I want Biking to appear before Skiing. Changing
the
> item's MenuItemID is not an option. So, I'm using a DisplayOrder column.
I
> need to integrate the DisplayOrder column into my stored procedure so that
> it returns the hierarchy in the correct order. I'm not sure what I'm
doing
> wrong, but I just can't seem to get this part working.
> Any help would be appreciated.
> Thanks,
> Brian
>
> CREATE TABLE MenuItem
> (
> MenuItemID INT,
> ItemName VARCHAR(30),
> ParentID INT,
> DisplayOrder INT
> )
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (1, 'Activities', NULL, 0)
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (2, 'Fishing', 1, 0)
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (3, 'Skiing', 1, 1)
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (4, 'Hiking', 1, 2)
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (5, 'Biking', 1, 3)
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (6, 'Swimming', 1, 4)
>
> CREATE PROCEDURE spCreateMenu (
> @.TargetMenuItemID INT
> )
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.MenuItemID INT,
> @.ItemName VARCHAR(30),
> @.ParentID INT,
> @.DisplayOrder INT
> SELECT @.MenuItemID=MenuItemID, @.ItemName=ItemName, @.ParentID=ParentID
FROM
> MenuItem WHERE MenuItemID = @.TargetMenuItemID
> PRINT REPLICATE('-', @.@.NESTLEVEL * 4) + @.ItemName
> SET @.MenuItemID = (SELECT MIN(MenuItemID) FROM MenuItem WHERE ParentID =
> @.TargetMenuItemID)
> WHILE @.MenuItemID IS NOT NULL
> BEGIN
> EXEC spCreateMenu @.MenuItemID
> SET @.MenuItemID = (SELECT MIN(MenuItemID) FROM MenuItem WHERE ParentID =
> @.TargetMenuItemID AND MenuItemID > @.MenuItemID)
> END
> END
>
> EXEC spCreateMenu 1
>