/****** 
    Object:  UserDefinedFunction [dbo].[fn_GetChildTreeStructure]    
    Script Date: 08/16/2008 14:03:45 
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_Edumatic3_GetChildTreeStructure] 
(
	@ParentTreeStructureId INT,
	@Index INT
)
RETURNS INT
AS
BEGIN
	DECLARE @TreeStructureId INT
	DECLARE @RightValue INT
	DECLARE @RootLeftValue INT
	DECLARE @RootRightValue INT
	DECLARE @TreeId INT
	
	SELECT	@RootLeftValue = LeftValue, 
                        @RootRightValue = RightValue, 
                        @TreeId = TreeId
	FROM	        TreeStructures
	WHERE	TreeStructureId = @ParentTreeStructureId

	SELECT	@RightValue = RightValue, 
			@TreeStructureId = TreeStructureId 
	FROM	        TreeStructures 
	WHERE	TreeId = @TreeId AND 
			LeftValue = @RootLeftValue + 1

	WHILE (@RightValue < (@RootRightValue - 1) AND @Index > 0) 
		BEGIN
			SELECT	@RightValue = RightValue, 
					@TreeStructureId = TreeStructureId 
			FROM	        TreeStructures 
			WHERE	TreeId = @TreeId AND 
					LeftValue = @RightValue + 1	
			
                       SET		@Index = @Index -1	
		END
	
	IF @Index <> 0
                -- Either negative index was passed or index was bigger than number of childs
		BEGIN
			SET @TreeStructureId = -1
		END

	RETURN @TreeStructureId

END