Click here to Skip to main content
11,413,548 members (71,203 online)
Click here to Skip to main content
Articles » Database » Database » SQL Server » Downloads
Add your own
alternative version

T- SQL - How to get all descendants of a given element in a hierarchical table

, 13 May 2009 CPOL
This article provides a T-SQL table valued function that retrieves all the descendant rows of a given row in a hierarchical table.
SCRIPT.zip
USE [master]
GO
/****** Object:  Database [PROVA]    Script Date: 04/26/2009 17:11:38 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'HierarchicalTest')
DROP DATABASE [HierarchicalTest]
GO
CREATE DATABASE [HierarchicalTest]
GO
USE [HierarchicalTest]
GO


USE [HierarchicalTest]
GO

/****** Object:  Table [dbo].[Category]    Script Date: 04/26/2009 17:46:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Category](
	[ParentName] [varchar](20) NULL,
	[Name] [varchar](20) NOT NULL,
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
	[Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Category]  WITH CHECK ADD  CONSTRAINT [FK_Category_Category] FOREIGN KEY([ParentName])
REFERENCES [dbo].[Category] ([Name])
GO

ALTER TABLE [dbo].[Category] CHECK CONSTRAINT [FK_Category_Category]
GO



GO

	
-- Next stored procedure is used to populate table with test data
CREATE PROC spCreateChilds

@ParentName		varchar(20),
@BaseName		varchar(20),
@Level			integer,
@MaxDepth		integer,
@ChildNumber	integer
		
AS

IF @Level < @MaxDepth
	BEGIN

		DECLARE @I Integer

		SET @I = 1

		WHILE (@I < @ChildNumber AND @ParentName IS NOT NULL) OR @I = 1
			BEGIN
	
				INSERT INTO Category
				(Name, ParentName)
				VALUES		
				(ISNULL(@BaseName, '') + CASE WHEN @BaseName IS NULL THEN '' ELSE '.' END  + CAST(@I as varchar), @ParentName)
	
				DECLARE @ExecParentName varchar(20)
				DECLARE @ExecBaseName varchar(20)
				DECLARE @ExecLevel int

				SET @ExecParentName = ISNULL(@BaseName, '') + CASE WHEN @BaseName IS NULL THEN '' ELSE '.' END  + CAST(@I as varchar)
				SET @ExecBaseName = ISNULL(@BaseName, '') + CASE WHEN @BaseName IS NULL THEN '' ELSE '.' END  + CAST(@I as varchar)
				SET @ExecLevel = @Level +1


				EXECUTE [dbo].[spCreateChilds] 
						@ExecParentName
					   ,@ExecBaseName
					   ,@ExecLevel
					   ,@MaxDepth
					   ,@ChildNumber
	
				SET @I = @I + 1

			END				
	END

GO


-- Now we create the two functions that will perform the job

-- This function retrieves all the ascendants
CREATE FUNCTION [dbo].[GetAscendantCategories] 
(	
	@CategoryName varchar(20)
)
RETURNS @Result TABLE  (Name varchar(20))
AS
	BEGIN

	WHILE @CategoryName IS NOT NULL
		BEGIN
			INSERT INTO @Result
			SELECT	@CategoryName
			
			SELECT 	@CategoryName = ParentName
			FROM 	dbo.Category
			WHERE   Name = @CategoryName
	
		END
	
	RETURN 	
END

GO

--This function retrieves all the descendants
CREATE FUNCTION [dbo].[GetDescendantCategories] 
(	
	@CategoryName varchar(20)
)
RETURNS @Result TABLE  (Name varchar(20))
AS
	BEGIN

	INSERT INTO @Result
	SELECT 
			Name
	FROM 	
			dbo.Category C
	WHERE 	@CategoryName in (SELECT P.Name FROM GetAscendantCategories(C.Name) P)
	
	RETURN 	
END


GO 
-- Let's populate Category table with test data
EXECUTE [dbo].[spCreateChilds] 
						NULL
					   ,NULL
					   ,1
					   ,4
					   ,30



-- Let's test our function


SELECT * FROM [dbo].[GetDescendantCategories] ('1.2') 

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Paolo Costa
Software Developer (Senior)
Italy Italy
Paolo Costa is a software developer with long experience on any kind of .NET application. He lives in Italy and works in Switzerland for a credit card payment acquiring company.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150427.1 | Last Updated 13 May 2009
Article Copyright 2009 by Paolo Costa
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid