问题不难，怎样让自定义函数返回表变量？？

[cityhunter172]

CREATE function TransferData
(@Service varchar (20))
returns Table
as
begin
declare @tmp table (s varchar(10))
if(CharIndex('MSN',@Service,1)>0)
begin
insert into @tmp values('MSN');
end
if(CharIndex('QQ',@Service,1)>0)
begin
insert into @tmp values('QQ');
end
if(CharIndex('ICQ',@Service,1)>0)
begin
insert into @tmp values('ICQ');
end
return @tmp
end

[cityhunter172]

以上報錯
應該怎么改﹖﹖

[xluzhong]

CREATE function TransferData
(@Service varchar (20))
returns @result table (s varchar(10))
as
begin
declare @tmp table (s varchar(10))
if(CharIndex('MSN',@Service,1)>0)
begin
insert into @tmp values('MSN')
end
if(CharIndex('QQ',@Service,1)>0)
begin
insert into @tmp values('QQ')
end
if(CharIndex('ICQ',@Service,1)>0)
begin
insert into @tmp values('ICQ')
end
insert into @result select * from @tmp
return
end
go

select * from test.dbo.transferdata('TESTMSNQQICQTEST')

drop function transferdate

[zjcxc]

--楼主这种函数属于多语句表值函数

--应该这样
CREATE function TransferData
(@Service varchar (20))
returns @tmp table (s varchar(10)) --returns中应该定义要返回的表变量
as
begin
if(CharIndex('MSN',@Service,1)>0)
begin
insert into @tmp values('MSN');
end
if(CharIndex('QQ',@Service,1)>0)
begin
insert into @tmp values('QQ');
end
if(CharIndex('ICQ',@Service,1)>0)
begin
insert into @tmp values('ICQ');
end
return --直接return
end
go

--调用
select * from TransferData('MSN,ICQ')

[zjcxc]

--也可以改成 内嵌表值函数

--应该这样
CREATE function TransferData
(@Service varchar (20))
returns table
as
return(
select top 100 percent
s=cast(s as varchar(10))
from(
select id=1,s='MSN' union all
select id=2,s='QQ' union all
select id=3,s='ICQ'
)a where CharIndex(s,@Service,1)>0
order by id)
go

--调用
select * from TransferData('MSN,ICQ')

[zjcxc]

后面写法的好处是,如果有更多的项,只需要按照规则加在
select id=2,s='QQ' union all
中就行了,而且在效率上,比使用多个if+insert 快

[cityhunter172]

謝謝以上兩位﹗﹗

祝大家在新的一年里

官位職稱步步高
銀行帳戶多鈔票