WITH Tree AS
	(
		
		SELECT Parent.ID			[ID]
		         ,  Parent.ParentID		[ParentID]
		FROM   Entity Parent
		WHERE  Parent.ID = @Parammeter
		
	UNION ALL
		
		SELECT Child.ID			
		         ,  Child.ParentID				
		FROM Entity Child
		INNER JOIN Tree	ON Child.ParentID = Tree.ID		
	)


	SELECT * 
	FROM Tree

With MyCTE (PRNT,CHLD,POS)
AS
(
 SELECT '1050000','1000000','1' UNION ALL
 SELECT '1050000','5000000','2' UNION ALL
 SELECT '1050001','1000004','1' UNION ALL
 SELECT '1050001','5000002','2' UNION ALL
 SELECT '1000000','1010000','1' UNION ALL
 SELECT '1000000','1010001','2' UNION ALL
 SELECT '1000000','3030000','3' UNION ALL
 SELECT '1010000','2020000','1' UNION ALL
 SELECT '1010001','2030000','1' 
 )

select * from MyCTE where
PRNT
 in
('1050000',
'1000000',
'1010000',
'1010001')
order by case
PRNT 
when '1050000' then 1
when '1000000' then 2
when '1010000' then 3
when '1010001' then 4
end , POS ASC

CREATE TABLE #Test
(
	ID			INT,
	ParentID	INT,
	TName		VARCHAR(25)
)
INSERT INTO #Test
(	
	ID,
	ParentID,
	TName	
)

SELECT 1,NULL,'Granfather'
UNION ALL
SELECT 2,1,'father1'
UNION ALL
SELECT 3,1,'father2'
UNION ALL
SELECT 4,2,'1st-son-father1'
UNION ALL
SELECT 5,2,'2nd-son-father1'
UNION ALL
SELECT 6,3,'1st-son-father2'
UNION ALL
SELECT 7,3,'2nd-son-father2'


SELECT * FROM #Test

--------------------------------------------
-- Recursive CTE
;WITH Tree AS
	(
		
		SELECT Parent.ID		[ID],
		       Parent.ParentID	[ParentID],
			   Parent.TName	
		FROM   #Test Parent
		WHERE  Parent.ID =2--@ID -- Pass the Top Id you want to see children of it.
		
	UNION ALL
		
		SELECT Child.ID	,		
		       Child.ParentID,
			   Child.TName						
		FROM #Test Child
		INNER JOIN Tree	ON Child.ParentID = Tree.ID		
	)


	SELECT * 
	FROM Tree


DROP TABLE #Test