返回表的自定义函数怎样写?

[zsanhong]

有一个部门表Branch的字段是部门人员编号EmpIDs,它记录的是部门员工的分布编号,编号之间用";"号分隔,
如下:
BranchID EmpIDs

1 12001;12004;12005;
2 12007;12008;
....
想达到这样的目的:写一个自定义函数,由EmpIDs的值返回该部门的员工编号和姓名值
EmpID Name branchID
12001 李在 1
12004 张中要 1
12005 高明 1
每个职工的编号是12001开始的5位数字,另有一个表Employee记录的是职工编号与姓名的对应关系

EmpID Name
12001 李在
12002 王芳
12004 张中要
12005 高明
...

[zsanhong]

在线等待中...

[zsanhong]

高手们请出招!!!

[YiOnLine]

不会写函数的规则?

[bernice99]

select a.EmpID,a.Name,b.branchID from
Employee a,Branch b
where a.EmpID like a.EmpIDs'%' or '%'a.EmpIDs'%'

[bernice99]

呵呵，不对别说我

[bernice99]

sorry,刚才写错了

select a.EmpID,a.Name,b.branchID from
Employee a,Branch b
where b.EmpIDs like a.EmpID'%' or '%'a.EmpID'%'

[zsanhong]

没关系.不过好象不行

[zsanhong]

有语法错误.

[ywh25]

create function GetEmpInfoByEmpId (@EmpId varchar(5))
returns @EmpInfo table
( EmpId varchar(5),
Name varchar(20),
branchID int
)
as
begin
insert @EmpInfo
select a.EmpID,a.Name,b.branchID from
Employee a,Branch b
where a.EmpID=@EmpId and charindex(a.EmpID,b.EmpIDs)>0
return
end

[dashou_hs]

select empid, name, 0 as branchid into NewEmployee from Employee
declare @branchid int,
@empid char(5)
declare cur_emp cursor for select empid from NewEmployee
open cur_emp
if @@error = 0
begin
fetch cur_emp into @empid
while @@fetch_status = 0
begin
select @branchid = branchid from EmpIDs where charindex( @empid, empids) > 0
update NewEmployee set branchid = @branchid where empid = @empid
fetch cur_emp into @empid
end
end
close cur_emp
deallocate cur_emp

[zsanhong]

测试中

[cxingh]

create table #a(BranchID int, EmpIDs varchar(100))
insert into #a select
1 , '12001;12004;12005;' union select
2, '12007;12008'

Create table #b(empid char(10), name varchar(20))
insert into #b select
'12001 ','李在' union select
'12002 ','王芳'union select
'12004' ,'张中要'union select
'12005', ' 高明'


select b.empid, name, BranchID from
#a a , #b b
where charindex( rtrim(b.empid), a.EmpIDs)>0

[yjdn]

--做成过程不是更简单吗？
--建立过程
create proc proc_try
@b varchar(10)
as
declare @a varchar(100)
select @a=replace(empids,';',' as aa union select '+@b+' as tt ,') from branch where branchid=@b
exec ('select a.empid,a.name,b.tt as branchID from employee a,(select '+@b+'as tt,'+@a+') b where a.empid=b.aa')

--执行
exec proc_try '1'
--结果
EmpID Name branchID
12001 李在 1
12004 张中要 1
12005 高明 1

[zanglinfeng]

存储过程更简单.

[searoom]

同意 ywh25(不悔)

[zsanhong]

大家好象都误解了本人的意思.参数是EmpIDs,即'12001;12004;12005;'
,假设自定义函数是GetEmpInfoByEmpId(),
如果调用GetEmpInfoByEmpId(12001;12004;12005;),得到的是
EmpID Name branchID
12001 李在 1
12004 张中要 1
12005 高明 1

ywh25(不悔)及yjdn(无尽天空)的参数都是empid.

[zsanhong]

综合大家的意见,我自己解决了此问题.如下:

create function GetEmpInfoByEmpId (@EmpIds varchar(1000))
returns @EmpInfo table
( EmpId varchar(5),
Name varchar(20),
branchID int
)
as
begin
insert @EmpInfo
select distinct a.EmpID,a.Name,b.branchID from
Employee a,Branch b
where a.EmpID=@EmpId and charindex(a.EmpID,@EmpIDs)>0
return
end

谢谢各位.马上结分.