sql 递归显示所有父节点

1.我先建两个表 一个表示项目及级别 另一个表示项目最后一级中包含内容。两个表的数据如图

CREATE TABLE [dbo].[yq_Project](

    [ID] [int] IDENTITY(1,1) primary key,

    [cCode] [varchar](32) NOT NULL,

    [cPriorCode] [varchar](32) NULL,

    [cName] [nvarchar](32) NULL,

    [cPersonName] [nvarchar](15) NULL,

    [cAddr] [nvarchar](20) NULL,

    [cTel] [varchar](20) NULL,

    [bHead] [bit] NULL,

    seq int )

alter table yq_project add cMemo nvarchar(128)

create table yq_Qz_House

(

id int identity(1,1) primary key,

cHuHao varchar(16),

cDongHao varchar(16),

cZuoLuo nvarchar(64),

cJieGou nvarchar(32),

cCengCi varchar(16),

cWeiZhi nvarchar(32),

cChaoXiang nvarchar(16),

iYuanLuo decimal(18,2),

dJunGongShiJian datetime,

cLeiBie nvarchar(16),

iZhuFang decimal(18,2),

iFengBiYangTai decimal(18,2),

iWeiFengYangTai decimal(18,2),

iGongYongFenTan decimal(18,2),

cProCode varchar(32)

)

2.新建一个函数 查找父节点

if exists(select name from sysobjects where name='Prants')

 drop function Prants

go

create function Prants(@cCode varchar(32))

returns varchar(100)

as

begin

 declare @str varchar(100)

 set @str=''

 declare @num varchar(32)

 select @str= cName from yq_project  where cCode=@cCode

 if exists(select cPriorCode from yq_project where cCode=@cCode)

 begin

  select @num=cPriorCode from yq_project where cCode=@cCode

  set @str=@str+'/'+dbo.Prants(@num)

 end

 return @str

end

执行查询

select dbo.Prants(cProCode) cProName,*  from yq_Qz_House where dbo.Prants(cProCode) like '%棋赛%'

结果如下图