INFOHPREET

DECLARE @Table TABLE(

        ID INT,

        ParentID INT,

        NAME VARCHAR(20)

)

INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 1, NULL, 'A'

INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 2, 1, 'B-1'

INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 3, 1, 'B-2'

INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 4, 2, 'C-1'

INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 5, 2, 'C-2'

DECLARE @ID INT

SELECT @ID = 2

;WITH ret AS(

        SELECT  *

        FROM    @Table

        WHERE   ID = @ID

        UNION ALL

        SELECT  t.*

        FROM    @Table t INNER JOIN

                        ret r ON t.ParentID = r.ID

)

SELECT  *