USE [DB_NAME]
GO
 
/****** Object:  UserDefinedFunction [DB_NAME].[GetDescendants]    Script Date: 04/14/2009 16:25:38 ******/
SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION [DB_NAME].[GetDescendants] ( @SiteID INT, @NavID int  )
RETURNS  @descendants TABLE
(
	ID  int NOT NULL,
	Ttitle varchar(100) NOT NULL,
	Depth int NOT NULL,
	ParentID int,
	ORDER int
)
AS
 
BEGIN
	DECLARE @level int
	SET @level = 1
 
	-- Add seed node
	INSERT @descendants
	(
		ID,
		Title,
		Depth,
		ParentID,
		ORDER
	)
	SELECT
		ID,
		Title,
		@level,
		ParentID,
		ORDER
	FROM
		dbo.TABLE_NAME
	WHERE
		(
			ID = @NavID
			OR
			 (
				@NavID IS NULL
				AND
				ParentID IS NULL
			 )
		)
		AND SiteID = @SiteID
 
	-- Populate descendants
	WHILE @@ROWCOUNT > 0
	BEGIN
		SET @level = @level + 1
		INSERT @descendants
		(
			ID,
			Title,
			Depth,
			ParentID ,
			ORDER
 
		)
		SELECT
			ID,
			Title,
			@level,
			ParentID,
			ORDER
		FROM
			dbo.TABLE_NAME
		WHERE
			 ParentID IN
			(
				SELECT
					ID
				FROM 
					@descendants
				WHERE
					depth = @level - 1
			)
			AND ID NOT IN 
			(
				SELECT
					ID
				FROM
					@descendants
				WHERE
					depth = @level - 1
			)
			AND ParentID IS NOT NULL
	END
	RETURN
END
GO