拆分列（ID的集合）的字符串，根据拆分结果查询所对应ID的名称

 /*
author:daiyueqiang
date  :2009-11-8
comment:拆分列（ID的集合）的字符串，根据拆分结果查询所对应ID的名称
*/

--建测试表
if object_id('RoleList') is not null
   drop table RoleList
go
create table Rolelist
(
id int,
rolename varchar(50)
)
go

if object_id('UserList') is not null
   drop table UserList
go

create table UserList
(
id int,
username varchar(50),
rolelist varchar(50)--可以属于多个角色
)
go

--建测试数据
insert into RoleList
select 1,'教师' union all
select 2,'高级教师' union all
select 3,'讲师' union all
select 4,'教授'
insert into UserList
select 1,'张三','1,2' union all
select 1,'李四','1,2,3' union all
select 1,'王五','1,4' union all
select 1,'赵六','1,3,4'

go

--根据ID列表获得名称列表
create function GetNameList(@idList varchar(50),@seprate char(1))
returns varchar(500)
as
begin
--名称列表
declare @nameList varchar(500)
declare @start int
declare @end int
declare @length int

set @nameList='';
set @start=1;
set @end=charindex(@seprate,@idList)
set @length=len(@idList)
  while @length>=0
    begin
 set @nameList=@nameList+dbo.GetName(substring(@idList,@start,@end-1))+','
 set @length=@length-@end;
 set @start=@end;
 set @idList=substring(@idList,@end+1,@length)
 set @end=charindex(@seprate,@IDList)
 if @end=0
   begin
     if len(@idList)>0
  set @nameList=@nameList+dbo.GetName(@idList)
     break;
   end 
    end
return @namelist;
end
go

create function GetName(@id varchar(50))
returns varchar(50)
as
begin
--名称
declare @name varchar(50)

select @name=rolename from RoleList where id=@id
return @name
end
go