很牛的储存过程

t1对应值 
a         b 
------------- 
a1        b1 
a2        b1,b2 
a3        b3 
a4        b5,b9,b10 

t2对应值 
b         c 
------------- 
b1        c1 
b2        c2 
b3        c3 
b4        c4 
b5        c5 
....
b10       c10


t1中的b字段和t2中的b字段里的内容有关联的 
我想根据b字段的关联查询得到下面的结果 
t3 
a         d 
-------------- 
a1        c1 
a2        c1,c2 
a3        c3 
a4        c5,c9,c10 

 

if object_id('t1') is not null
   drop table t1
if object_id('t2') is not null
   drop table t2
if object_id('f_getall') is not null
   drop function f_getall
go
create table t1(a varchar(10),b varchar(10))
create table t2(b varchar(10),c varchar(10))
insert into t1 values('a1','b1')
insert into t1 values('a2','b1,b2')
insert into t1 values('a3','b3')
insert into t1 values('a4','b5,b9,b10')

insert into t2 values('b1','c1')
insert into t2 values('b2','c2')
insert into t2 values('b3','c3')
insert into t2 values('b4','c4')
insert into t2 values('b5','c5')
insert into t2 values('b6','c6')
insert into t2 values('b7','c7')
insert into t2 values('b8','c8')
insert into t2 values('b9','c9')
insert into t2 values('b10','c10')
go
select * from t1
select * from t2

create function f_getall
(@b varchar(10))
returns varchar(1000)
as
begin
    declare @s varchar(1000)
    declare @now_b varchar(1000),@DH_i int
    set @now_b=@b
    if(charindex(',',@now_b)>0)
      begin
        while(charindex(',',@now_b)>0)
        begin
          set @DH_i=charindex(',',@now_b)
          declare @b_c varchar(100)
          set @b_c=substring(@now_b,1,@DH_i-1)
          select @s=isnull(@s,'')+c+',' from t2 where b=@b_c
          set @now_b=substring(@now_b,@DH_i+1,len(@now_b))
        end
        set @s=@s+@now_b
      end
    else
      begin
         select @s=isnull(@s,'')+c from t2 where b=@now_b
       end
return @s 
end
go
--执行结果语句
select a,dbo.f_getall(b) as d from t1