Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Reporting a menu structure using a recursive UDF

By Stephen Lasham,

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

OptionPrimaryKey

ParentForeignKey

OptionDescription

Level

1

NULL

Accounts


Parent

2

1

Reports


Child

3

2

Customers Report


Grandchild

4

2

Suppliers Report


Grandchild

5

1

Maintenance


Child

6

5

Customer Maintenance


Grandchild

7

5

Supplier Maintenance


Grandchild

8

NULL

Sales


Parent

9

8

Sales Representatives


Child

10

9

Sales Rep Report


Grandchild

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_in	varchar(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.OptionDescription	as 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

OptionDescription

MenuLevel

MenuPath

Accounts

0

Accounts

Reports

1

Accounts >> Reports

Customers Report

2

Accounts >> Reports >> Customers Report

Suppliers Report

2

Accounts >> Reports >> Suppliers Report

Maintenance

1

Accounts >> Maintenance

Customer Maintenance

2

Accounts >> Maintenance >> Customer Maintenance

Supplier Maintenance

2

Accounts >> Maintenance >> Supplier Maintenance

Sales

0

Sales

Sales Representatives

1

Sales >> Sales Representatives

Sales Rep Report

2

Sales >> Sales Representatives >> Sales Rep Report

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.

Total article views: 8522 | Views in the last 30 days: 3
 
Related Articles
FORUM

Parent Report and Sub Reports Issue

Parent Report and Sub Reports

BLOG

Creating a Recursive Report from a Parent Child Dimension

Reporting Services supports recursive hierarchies and Analysis Services supports parent-child dimen...

FORUM

FOR XML EXPLICIT - One to many relationship at level 3 (3 = parent, 4 = child)

Having trouble rendering a parent/child relationship at level 3/4 respectively.

FORUM

Hiding tables in a drill through report - based on parameters received from the parent report

Hiding tables based on parameters received from the parent report

FORUM

Return Parent Level entry using cte

return parent instead of parent-->Child-->GrandChid entry on project page.

Tags
sql puzzles    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones