create function getNames(@Rs varchar(100))
returns varchar(400)
as
Begin
declare @Str varchar(200),@i int,@Splits varchar(10),@Str_all varchar(400)
set @Splits= '| '
set @Str_all= ' '
Set @Rs = RTrim(LTrim(@Rs))
Set @i = CharIndex(@Splits,@Rs)
begin
While @i > = 1
Begin
select @Str=Ename from employs where Ecardid=Left(@Rs,@i-1)
set @[email protected][email protected]+ '| '
Set @Rs = SubString(@Rs,@i+1,Len(@Rs)[email protected])
Set @i = CharIndex(@Splits,@Rs)
End
--RETURN(@Str_all)
If @Rs <> ' '
begin
select @Str_all=Ename from employs where [email protected]
--RETURN(@Str_all)
end
Return
end
RETURN(@Str_all)
End
总是提示我:标量值的函数中的 RETURN 语句必须含有参数。
这个函数的目的,就是把 '1001|1002|1003 '转换成 '姓名1|姓名2|姓名3 '
我单独测试
create table #t(strNames varchar(400))
declare @Rs varchar(100),@Str varchar(200),@i int,@Splits varchar(10),@Str_all varchar(400)
set @Splits= '| '
select @Rs= '1001|1002|1003| ' --Sid=1
set @Str_all= ' '
print @Rs
--begin
Set @Rs = RTrim(LTrim(@Rs))
Set @i = CharIndex(@Splits,@Rs)
While @i > = 1
Begin
--insert into ReceiveMail(Rcardid,Sid) Values(Left(@Sql,@i-1),@Sid)
select @Str=Ename from employs where Ecardid=Left(@Rs,@i-1)
--print @Str
set @[email protected][email protected]+ '| '
--print @Str_all
Set @Rs = SubString(@Rs,@i+1,Len(@Rs)[email protected])
Set @i = CharIndex(@Splits,@Rs)
End
insert into #t values(@Str_all)
If @Rs <> ' '
--insert into ReceiveMail(Rcardid,Sid) Values(@Sql,@Sid)
select @Str_all=Ename from employs where [email protected]
Return
这样是能成功从select strNames from #t 取到我想要的东西
怎么写成函数就错啦,
谢谢!
------解决方案--------------------
http://community.csdn.net/Expert/topic/5417/5417442.xml?temp=.6191217
------解决方案--------------------
create function getNames(@Rs varchar(100))