Here is All you want, with dummy data,

CREATE TABLE #Test

(

IDINT,

ParentIDINT,

TNameVARCHAR(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 TreeON Child.ParentID = Tree.ID

)

SELECT *

FROM Tree

DROP TABLE #Test