用@@ROWCOUNT實現的SQL遞迴。

--> 測試資料: @T
declare @T table (id int,name varchar(12),fid int)
insert into @T
select 10001,'路容保潔',10000 union all
select 10002,'路面',10000 union all
select 10003,'路基',10000 union all
select 10004,'橋隧涵',10000 union all
select 10005,'沿線設施',10000 union all
select 10006,'機電設施',10000 union all
select 10007,'綠化',10000 union all
select 10008,'機械保潔',10001 union all
select 10009,'人工保潔',10001 union all
select 10010,'偶然事件',10001 union all
select 10011,'瀝青砼路面',10002 union all
select 10012,'水泥砼路面',10002 union all
select 10013,'附屬構造物',10002 union all
select 10014,'路基防護',10003 union all
select 10015,'路基水毀',10003 union all
select 10016,'路基排水',10003 union all
select 10017,'橋樑',10004 union all
select 10018,'涵洞',10004 union all
select 10019,'隧道',10004 union all
select 10020,'管理設施',10005 union all
select 10021,'安全防護設施',10005 union all
select 10022,'其他設施',10005 union all
select 10023,'橋面鋪裝',10017 union all
select 10024,'排水系統',10017 union all
select 10025,'附屬設施',10017 union all
select 10026,'伸縮縫',10017 union all
select 10027,'翼牆錐坡',10017 union all
select 10028,'墩台',10017 union all
select 10029,'台帽蓋梁',10017 union all
select 10030,'梁式構件',10017 union all
select 10031,'橋與路串連',10017

declare @R table (id int,name varchar(50),fid int)

INSERT INTO @R select * from @T T1  WHERE ID > 10022

WHILE @@ROWCOUNT>0
BEGIN
UPDATE R
SET R.NAME = T.NAME+'-'+R.NAME,R.FID = T.FID
FROM @T T,@R R WHERE R.FID = T.ID
END

SELECT * FROM @R

/*
----------結果------------

--id  name                        fid
10023 橋隧涵-橋樑-橋面鋪裝 10000
10024 橋隧涵-橋樑-排水系統 10000
10025 橋隧涵-橋樑-附屬設施 10000
10026 橋隧涵-橋樑-伸縮縫 10000
10027 橋隧涵-橋樑-翼牆錐坡 10000
10028 橋隧涵-橋樑-墩台 10000
10029 橋隧涵-橋樑-台帽蓋梁 10000
10030 橋隧涵-橋樑-梁式構件 10000
10031 橋隧涵-橋樑-橋與路串連 10000

*/