while(parentId <>00000)

begin
    declare @ID int
    select id  from tableName where id=
    (select parentId from tableName where id=@ID)
end

------解决方案--------------------

看楼主例子，作为顶级ID，parentId的值为‘00000’。
SQL code

create table t1
(
    id int,
    pid varchar(5),
    name varchar(10)
)
insert into t1
select 1, '00000', 'a' union all
select 2, '1', 'a.a' union all
select 3, '2', 'a.a.a' union all
select 4, '00000', 'b' union all
select 5, '4', 'b.b' union all
select 6, '5', 'b.b.b'
select * from t1

;with aaa as
(
select * from t1 where id=3
union all
select t1.* from t1 inner join aaa on t1.id=aaa.pid
)
select * from aaa where pid='00000'

--------------------
id    pid    name
1    00000    a