sql递归查询填充子数据

ALTER function GetChapterForChildId(@id varchar(21)) returns varchar(100)
as
begin
declare @re_str as varchar(100)
set @re_str = ''
SELECT @re_str = Chapter from dbo.tXC_XBRLUIConfig where id = @id
while @re_str='' OR @re_str='||' OR @re_str='|||'
begin
select @id = b.id , @re_str = b.Chapter from tXC_XBRLUIConfig a , tXC_XBRLUIConfig b WHERE a.id = @id AND a.pid = b.id
END
RETURN @re_str
END
GO