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

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')