在SQL Server的存储过程里如何使用递归

表是这样的
create table Channel_Info
(
[ChannelId] int identity(1,1) primary key,
[Title] nvarchar(10) not null,
[ParentId] int
constraint FK_ChannelInfo_ChannelId references Channel_Info([ChannelId]) --自引用
)

我想查出像这样的数据
[ChannelId] [Title]
1 a
2 ....a1(引用1)
3 ....a2(引用1)
4 ...........aa1 (引用3)
5 b
6 c
7 .....c1
要查出这样的数据应该怎样做，请各位大哥大姐帮个忙，小弟在此先谢过了.....

这个太复杂了，有没有简单点的

--树型结构处理之双编号（广度深度排序）
if OBJECTPROPERTY(object_id('tb'),'isusertable')<>0
drop table tb
create table tb(ybh nvarchar(10),ebh nvarchar(10),beizhu nvarchar(1000))
insert tb
select '0001',null,'云南省'
union all select '0002','0001','昆明市'
union all select '0003','0001','昭通市'
union all select '0009','0001','大理市'
union all select '0008',null,'四川省'
union all select '0004',null,'贵州省'
union all select '0005','0002','五华区'
union all select '0007','0002','水富县'
union all select '0006','0005','西园路192号'
union all select '0010','0006','金色梧桐3-702'
union all select '0011','0010','昆明越科时空科技有限公司'
union all select '0015','0007','两碗乡'
union all select '0013','0015','两碗村'
union all select '0012','0013','某跨国集团董事长'
union all select '0014','0008','成都市'

--深度排序（模拟单编码法)
declare @level_tt table(ybh nvarchar(1000),ebh nvarchar(1000),level int)
declare @level int
set @level=0
insert @level_tt(ybh,ebh,level)
select ybh,ybh,@level from tb where ebh is null
while @@ROWCOUNT>0
begin
set @level=@level+1
insert @level_tt(ybh,ebh,level)
select a.ybh,b.ebh+a.ybh,@level
from tb a,@level_tt b
where a.ebh=b.ybh and b.level=@level-1
end
select space(b.level*2)+'----'+a.beizhu,a.*,b.*
from tb a,@level_tt b
where a.ybh=b.ybh
order by b.ebh

with empscte as ( select employeeid, reportsto, firstname, lastname from dbo.employees where employeeid = 5 union all select emp.employeeid, emp.reportsto, emp.firstname, emp.lastname from empscte as mgr join dbo.employees as emp on emp.reportsto = mgr.employeeid ) select * from empscte; 这就是一个使用cte的一个查询,你看一下吧