SQL SERVER函数使用一例

Create Table A( Id int, Name varchar(5), ProId varchar(10))
insert into A
select 1, '甲', '1,2,3' union
select 2 , '乙', '2,3'
Create Table B(ProId int,name varchar(10))
insert into B
select 1,'物品1' union
select 2,'物品2' union
select 3,'物品3' union
select 4,'物品4'
------------------實現-------------------------------------------
Create function getk(@id int)
returns varchar(500)
as
begin
declare @s varchar(500)
set @s=''
select @s=@s+b.name+',' from a left join b on charindex(cast(b.proid as varchar(2)),a.proid)>0 where a.id=@id
set @s=left(@s,len(@s)-1)
return(@s)
end
---------------------------------------------
select id,name,dbo.getk(id) from a
----------------結果---------------------------------------------

1 甲 物品1,物品2,物品3
2 乙 物品2,物品3