Overview

You work with a system that uses a menu, but which lacks any reporting of the menu structure. You require a way to represent the menu structure for output but due to each option being its own record, with a parent reference as a foreign key, this is difficult. What you need is a single line representation for each option, showing where it sits in

the menu tree.

Context

After spending some time searching the internet for a simple solution I decided to design my own. The output structure is as follows. SQL needs to produce the text string for each line, with indentation handled by the presentation layer software.

Parent

Parent >>

child

Parent

>> child >> grandchild

Parent

>> child >> grandchild

Parent >>

child

Parent

>> child >> grandchild

Parent

>> child >> grandchild

Parent

Parent >>

child

Parent

>> child >> grandchild

Etc

The menu table (MenuMaster) has the following columns

• OptionPrimaryKey
• ParentForeignKey
• OptionDescription

The first

thing to consider is that any menu structure such as that shown

above, can have any number of levels and as such is ideally suited to

a recursive function to provide the necessary output (up to the

recursion limit of your version of SQL).

The next

thing to identify is that any function for this process can have only

one parameter (at least for SQL/2000, being the version I am using).

This

presents a problem until you work out that the single parameter can

be a concatenation of many different values and can be sliced and

diced as required.

In order

to make the recursion work and provide the required output, I need to

know three things.

1. The primary key of the option
2. The level number of the option
3. The text string of the option path

My parameter string structure is as follows.

Characters 1 to 9 = OptionPrimaryKey

Characters 10 and 11= level

Characters 12 to 1024= option path

I have defined my input string as varchar(1024) for my purpose but any length long enough to hold the maximum menu path length can be used.

The UDF code

-- First drop the function if it already exists (so we can get a clean build)
IF exists ( select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_Build_Menu_String]') 
               and OBJECTPROPERTY(id, N'IsInlineFunction') is not null)
DROP FUNCTION [dbo].[fn_Build_Menu_String]
GO
-- Create the function
CREATE FUNCTION dbo.fn_Build_Menu_String
(
@Menustring_invarchar(1024)
)
RETURNS varchar(1024)
AS
BEGIN
-- Declare variables
Declare @ParentForeignKey as int
Declare @level as int
Declare @OptionDescription as varchar(50)
Declare @Menustring_out as varchar(1024)
-- Initialise the level number (on the first pass this will be blank)
Set @level = case when isnumeric(substring(@Menustring_in, 10, 2)) = 1
then cast(substring(@Menustring_in, 10, 2) as int)
else 0 end
-- Get the current menu parent key and option description
Select @ParentForeignKey = ParentForeignKey
, @OptionDescription = rtrim(OptionDescription)
from MenuMaster
where OptionPrimaryKey = cast(left(@Menustring_in,9) as int)
-- If we have reached the top level (parent is null)
-- Prepare the final output string (being the level number and path string)
If @ParentForeignKey is null
 Begin
Set @Menustring_out = right('00' + cast(@level as varchar(2)),2)
+ rtrim(@OptionDescription)
+ substring(@Menustring_in,12,1024)
  End
-- If we are not yet at the top level (parent is not null)
-- Build the output string prefixed by the parent number 
--  and feed back through the UDF
else 
  Begin
Set @level = @level + 1
Set @Menustring_out =  right('000000000' + cast(@ParentForeignKey as varchar(9)),9)
+ right('00' + cast(@level as varchar(2)),2)
+ ' >> ' -- option separator
+ rtrim(@OptionDescription)
+ substring(@Menustring_in,12,1024)
Set @Menustring_out = dbo.fn_Build_Menu_String(@Menustring_out)
 End
--  having reached the top level and built the final string, exit the UDF
RETURN @Menustring_out
END
GO

Executing the code

To create the sample menu table execute the following code

Create table dbo.MenuMaster
(OptionPrimaryKey int
, ParentForeignKey int
, OptionDescription varchar(100)
)
Insert into dbo.MenuMaster values (1, null, 'Accounts')
Insert into dbo.MenuMaster values (2, 1, 'Reports')
Insert into dbo.MenuMaster values (3, 2, 'Customers Report')
Insert into dbo.MenuMaster values (4, 2, 'Suppliers Report')
Insert into dbo.MenuMaster values (5, 1, 'Maintenance')
Insert into dbo.MenuMaster values (6, 5, 'Customer Maintenance')
Insert into dbo.MenuMaster values (7, 5, 'Supplier Maintenance')
Insert into dbo.MenuMaster values (8, null, 'Sales')
Insert into dbo.MenuMaster values (9, 8, 'Sales Representatives')
Insert into dbo.MenuMaster values (10, 9, 'Sales Rep Report')

To call the function execute the following code

Select rtrim(OptionDescription) as OptionDescription
, cast(left(Menustring,2) as int) as MenuLevel
, substring(Menustring, 3, 1024) as MenuPath
 from ( Select dbo.fn_Build_Menu_String (cast(OptionPrimaryKey as varchar(9))) as MenuString
, Menu.OptionDescriptionas OptionDescription
   from MenuMaster as Menu
) as Menu
order by substring(Menustring,3,1024)

The process in detail

The nature of a recursive UDF is to repeat, calling itself and building the desired result with each successive pass.

In this case, the above select script calls the UDF for each record in MenuMaster passing it the record’s primary key. This sits in position 1 to 9 of the input string, making it possible to extract this number for each pass through the UDF.

One of the first things the UDF does is initialise the level number from position 10 and 11 of the input string. On the first pass, this is blank and the level is set to zero, otherwise it is set to the value found so it can be incremented later.

-- Initialise the level number (on the first pass this will be blank)
Set @level = case when isnumeric(substring(@Menustring_in, 10, 2)) = 1
                  then cast(substring(@Menustring_in, 10, 2) as int)
                else 0 
             end

The UDF next determines the parent foreign key value and the text of the current menu option. This is done by extracting the primary key of the current record from the input string (positions 1 to 9), and setting values from this record.

-- Get the current menu parent key and option description
Select @ParentForeignKey = ParentForeignKey
, @OptionDescription = rtrim(OptionDescription)
 from MenuMaster
 where OptionPrimaryKey = cast(left(@Menustring_in,9) as int)

The UDF now knows if it has a top level (parent is null) or a child level record (parent is not null)

If the parent key is null, it is a top-level option and the string is formatted for final output of;

-Characters 1 to 2, the level number

-Characters 3 to 1024, the formatted menu path

-- If we have reached the top level (parent is null)
-- Prepare the final output string (being the level number and path string)
If @ParentForeignKey is null
 Begin
  Set @Menustring_out =  right('00' + cast(@level as varchar(2)),2)
+ rtrim(@OptionDescription)
+ substring(@Menustring_in,12,1024)
End

This sets the output string to be the level number of the current record, followed by the option description of the current record, followed by any option description built from earlier passes.

If the parent key is not null, then the record read is only part of the path and so must be saved and extended on subsequent passes.

-- If we are not yet at the top level (parent is not null)
-- Build the output string prefixed by the parent number 
-- and feed back through the UDF
else
Begin
Set @level = @level + 1
Set @Menustring_out = 
right('000000000' + cast(@ParentForeignKey as varchar(9)),9)
+ right('00' + cast(@level as varchar(2)),2)
+ ' >> ' -- option separator
+ rtrim(@OptionDescription)
+ substring(@Menustring_in,12,1024)
Set @Menustring_out = dbo.fn_Build_Menu_String(@Menustring_out)
End

The Option description includes;

- Characters 1 to 9 as the parent key, ready for extracting on the next pass,

- Characters 10 and 11, the level number (incrementing the earlier initialised value by 1)

- Characters 12 to 1024, an option separator (any value will do, I have selected “>>”), followed by the current options menu description, followed by the cumulative menu description from any previous pass.

The above code does an interesting thing with the number to ensure positions 1 to 9 represent the parent key with leading zeros. It takes the key and concatenates it to a prefix of nine zeros. So for example, a value of one becomes “0000000001” (ten characters). It then takes the right most nine characters of this to get “000000001” (nine characters).

This new description is passed recursively to the UDF so that it can look up the next higher level of the menu path and build it in to the front of the menu string.

The resulting output is

Summary

In this text, I have shown the use of a recursive SQL for building a menu path string for reporting purposes. The presentation software, taking into account the menu level number (also extracted), handles indentation.

This article does not try to demonstrate any error trapping, which for a robust application should be included. This assumes therefore that the maximum level of recursion will not be reached and that the database is accurate with correct representation of all parent child relationships.

The principles of this UDF can be used to represent other such parent child relationships, such as for a bill of materials or an employee relationship table.

The UDF shows how you can cleverly slice and dice a single parameter into multiple parameters and use this to build a multiple field output string that can be sliced to get the individual components held within. This is a valuable technique for working with recursive UDFs.

In addition, before anyone says I am wrong with my use of 1024 for all lengths on the substring, I know this is wrong in places due to various string lengths after the concatenations, but have left it as 1024 because as long as it is greater than the string length it will work.