create table a(a varchar(10),b varchar(10))
insert into a select 'a','b'
insert into a select 'b','c'
insert into a select 'c','d'
insert into a select 'd','e'

create table b(c varchar(10),d varchar(10))
insert into b select 'aa','a'
insert into b select 'bb','b'
insert into b select 'dc','c'
insert into b select 'dd','d'
insert into b select 'ee','e'



create function f_cid(@b varchar(10))
returns varchar(500)
as
begin
     declare @t table(a varchar(10),b varchar(10),lev int)
     declare @lev int
     set @lev=1
     insert into @t select *,@lev from  a where b=@b
     while(@@rowcount>0)
     begin
          set @lev=@lev+1
          insert into @t select a.*,@lev from a a,@t b
          where a.b=b.a and b.lev=@lev-1
     end
     declare @cids varchar(500)
     select @cids=isnull(@cids+',','')+ltrim(b) from @t order by lev
     select top 1 @cids=@cids+','+a from @t order by lev desc
    return @cids
end



select * from B where  charindex(','+d+',',','+dbo.f_cid('e')+',')>0

------解决方案--------------------
CREATE TABLE tb (a char(8),b char(8))
CREATE TABLE tba (a char(8),b char(8))
insert into tb  
select 'A','B'
union all
select 'B','C'
union all
select 'C','D'
union all
select 'D','E'

insert into tba
select 'Aa','A'
union all
select 'bb','B'
union all
select 'Cc','C'
union all
select 'Dd','D'
union all
select 'ee','E'


CREATE FUNCTION 查询 (
@pp char(8) )
returns nvarchar(4000)
AS  
begin
declare @ii nvarchar(4000)
set @ii=(select top 1 a from tb where b=@pp)
if (@ii is not null)
set @ii=rtrim(@ii)+''' or b='''+rtrim(dbo.查询(@ii))
else
set @ii=''
return @ii
end

declare @a char(8),@nc nvarchar(4000)
set @a='E'
set @nc=dbo.查询(@a)
if (ltrim(@nc)<>'')
set @nc='select * from tba where b='''+@a+''' or b='''+left(@nc,len(@nc)-6)
else
set @nc= 'select * from tba where b='''+@a+''''
exec (@nc)

drop FUNCTION 查询
drop TABLE tb
drop TABLE tba