CREATE PROCEDURE [dbo].[dvadmin_GetTaskTree]
(
	@Tasks GuidList READONLY,
	@TaskGroups GuidList READONLY
)
AS
BEGIN
	SET NOCOUNT ON

	CREATE TABLE #Tasks
	(
		[TaskID] uniqueidentifier ,
		[TaskListID] uniqueidentifier,
		[ParentID] uniqueidentifier,
		[Level] int
	)

	CREATE CLUSTERED INDEX idx_tasks_taskid
	ON #Tasks ([TaskID])

	CREATE NONCLUSTERED INDEX idx_tasks_level
	ON #Tasks([Level]) include ([TaskListID])

	CREATE TABLE #TaskGroups
	(
		[TaskGroupID] uniqueidentifier,
		[TaskListID] uniqueidentifier,		
		[ParentID] uniqueidentifier,
		[Level] int
	)

	CREATE CLUSTERED INDEX idx_taskgroups_taskid
	ON #TaskGroups (TaskGroupID)

	CREATE NONCLUSTERED INDEX idx_taskgroups_level
	ON #TaskGroups([Level]) include (TaskListID)
 
	DECLARE @NullGuid uniqueidentifier = N'00000000-0000-0000-0000-000000000000', @True bit = 1, @False bit = 0
	declare @NullDate datetime, @NullText nvarchar(MAX), @NullInt int, @NullBit bit

	-- set start level and break indicator
	DECLARE @Level int = 0, @Go int = 1


	begin
		insert into #Tasks ([TaskID], [TaskListID], [Level])
		SELECT tTaskInfo.InstanceID, tTaskInfo.ChildTaskList, @Level
		FROM [dbo].[dvview_{20d21193-9f7f-4b62-8d69-272e78e1d6a8}] tTaskInfo WITH(NOLOCK)
		JOIN @Tasks tTasks ON (tTaskInfo.[InstanceID] = tTasks.[ID])              
                    
		insert into  #TaskGroups ([TaskGroupID], [TaskListID], [Level])
		SELECT tTaskGroupInfo.InstanceID, tTaskGroupInfo.TaskList, @Level
		FROM [dbo].[dvview_{7a6593bb-0bdb-4082-b43b-8f26594eff91}] tTaskGroupInfo WITH(NOLOCK)
		JOIN @TaskGroups tTaskGroups ON (tTaskGroupInfo.[InstanceID] = tTaskGroups.[ID])

		WHILE @Go > 0 
		BEGIN 
			--fetch next level
			SELECT  @Level = @Level + 1, @Go = 0

			INSERT INTO #Tasks (TaskID, TaskListID, [ParentID], [Level])
			select tTaskInfo.InstanceID, tTaskInfo.ChildTaskList, t.TaskGroupID, @Level
			from #TaskGroups t
			INNER JOIN [dbo].[dvview_{9bc6b0e9-a809-43f1-a27f-323a28d1b010}] tTaskListTasks WITH(NOLOCK) on (t.TaskListID = tTaskListTasks.InstanceID)
			INNER JOIN [dbo].[dvview_{20d21193-9f7f-4b62-8d69-272e78e1d6a8}] tTaskInfo WITH(NOLOCK, index(1) forceseek) ON (tTaskListTasks.[Task] = tTaskInfo.[InstanceID])
			WHERE t.[Level] = @Level -1

			if @@ROWCOUNT > 0
				set @Go = 1

			INSERT INTO #Tasks (TaskID, TaskListID, [ParentID], [Level])
			select tTaskInfo.InstanceID, tTaskInfo.ChildTaskList, tTaskInfo.ParentTask, @Level
			from #Tasks t
			INNER JOIN [dbo].[dvview_{20d21193-9f7f-4b62-8d69-272e78e1d6a8}] tTaskInfo WITH(NOLOCK, FORCESEEK) on (t.TaskID = tTaskInfo.ParentTask)
			WHERE t.[Level] = @Level -1

			if @@ROWCOUNT > 0
				set @Go = 1
			
			insert into  #TaskGroups ([TaskGroupID], [TaskListID], [ParentID], [Level])
			select tTaskGroupInfo.InstanceID, tTaskGroupInfo.TaskList, t.[TaskID], @Level
			from #Tasks t
			JOIN [dbo].[dvview_{4aed5aec-0ed8-4743-9c78-63d47128455e}] tTaskListTaskGroups  WITH(NOLOCK) on (t.TaskListID = tTaskListTaskGroups.InstanceID)
			JOIN [dbo].[dvview_{7a6593bb-0bdb-4082-b43b-8f26594eff91}] tTaskGroupInfo WITH(NOLOCK) on (tTaskListTaskGroups.TaskGroup = tTaskGroupInfo.InstanceID)
			WHERE t.[Level] = @Level -1
	 
			if @@ROWCOUNT > 0
				set @Go = 1

		END

		;with helpTasks([TaskID], [ParentID], [Level])
		as
		(
			select distinct [TaskID], [ParentID], [Level] from #Tasks
		),


		helpTaskGroups ([TaskGroupID], [ParentID], [Level])
		as
		(
			select distinct [TaskGroupID], [ParentID], [Level] from #TaskGroups

		),
		help ([TaskID], [TaskGroupID], [ParentItemID], [IsTaskGroup], [Level])
		as
		(
			SELECT objectsData.TaskID as [TaskID]
				,@NullGuid as [TaskGroupID]
				,objectsData.ParentID [ParentItemID]
				,@False [IsTaskGroup]
				,objectsData.Level as [LevelTask]
			FROM helpTasks objectsData
			
			union all
	
			SELECT @NullGuid as [TaskID]
				,objectsData.TaskGroupID as [TaskGroupID]
				,objectsData.ParentID as [ParentItemID]
				,@True as [IsTaskGroup]
				,objectsData.[Level] as [LevelTaskGroup]
			FROM helpTaskGroups objectsData
		)
		SELECT [TaskID], [TaskGroupID], [ParentItemID], [IsTaskGroup], [Level]
		FROM help
		ORDER BY Level ASC
	end	
END

GO

DECLARE @Tasks dbo.GuidList
INSERT INTO @Tasks VALUES('2e69e7c7-6480-4413-95ae-baa1835af1e86')

EXEC [dbo].[dvadmin_GetTaskTree] @Tasks