Tree Walking With Recursive Function

Posted: February 1, 2009 in SQL Server 2005

To perform a tree walking routine to find all the top level menu options for a particular sub menu option, open a new query window and execute the following line of code:

-- Create a new test table
CREATE TABLE [MyTest] ([Test_PK]     INT IDENTITY
                      ,[Test_Menu]   VARCHAR(50)
                      ,[Test_Parent] INT);

-- Insert test data into the table
INSERT INTO [MyTest] VALUES ('Level A', NULL);
INSERT INTO [MyTest] VALUES ('Level B', NULL);
INSERT INTO [MyTest] VALUES ('Sub Level A', 1);
INSERT INTO [MyTest] VALUES ('Sub Level B', 2);
INSERT INTO [MyTest] VALUES ('Sub Sub Level A', 3);
INSERT INTO [MyTest] VALUES ('Sub Sub Level A', 3);
INSERT INTO [MyTest] VALUES ('Sub Sub Level B', 4);

-- Declare variables
DECLARE @Test_PK INT;

-- Initiliase variables
SET @Test_PK = 0;

-- Get the key for the level of the tree that you are after
SELECT @Test_PK = [Test_PK]
FROM   [MyTest]
WHERE  [MyTest].[Test_Menu] = 'Sub Sub Level B';

-- Start tree walking routine here
BEGIN
    WITH MenuLevels([Test_PK], [Test_Menu], [Test_Parent])
    AS
    (
     -- Anchor member
     SELECT [Test_PK]
           ,[Test_Menu]
           ,[Test_Parent]
     FROM   [MyTest]
     WHERE  [Test_PK] = @Test_PK
     UNION ALL
     -- Recursive member
     SELECT [MyTest].[Test_PK]
           ,[MyTest].[Test_Menu]
           ,[MyTest].[Test_Parent]
     FROM   [MyTest]
            JOIN [MenuLevels] ON [MenuLevels].[Test_Parent] = [MyTest].[Test_PK]
    );
    -- Outer query
    SELECT   *
    FROM     [MenuLevels]
    ORDER BY [Test_PK];
END;

-- Drop the table
DROP TABLE MyTest;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s