我在查询分析器中执行了如下SQL没有问题
declare @str varchar(10)
declare @code table (code varchar(10))
set @str ='123456789'
while(LEN(@str)>0)
begin
insert into @code select subString(@str,1,2)
set @str = subString(@str,3,LEN(@str))
print @str
end
select * from @code
但是当我定一个方法的时候,却一直报错,错误信息是:
Msg 178, Level 15, State 1, Procedure f_get_printer, Line 21
在此上下文中不能使用带有返回值的 RETURN 语句。
Msg 102, Level 15, State 31, Procedure f_get_printer, Line 25
'BEGIN' 附近有语法错误。
SQL如下
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[f_get_printer]
(
@codestr varchar(20
RETURNS TABLE
AS
begin
declare @code table (code varchar(10))
while(LEN(@codestr)>0)
begin
insert into @code select subString(@codestr,1,2)
set @codestr = subString(@codestr,3,LEN(@codestr))
end
RETURN
@code
end
GO
3 个解决方案
#1
用这个试试
/*
功能说明:传入字符串跟分割符('''SGHE00000003'',''SGHE00000004'',''SGHE00000005'''),返回一个Table
*/
ALTER function [dbo].[fnSys_SplitString]
(
---字符串分割
@Str varchar(max), --传入的字符串
@SeprateStr varchar(10)--分隔符
)
Returns @temp table(Code varchar(100)) --返回一个Table
As
Begin
Declare @i int
Set @Str=REPLACE(@str,'''','')
Set @Str =rtrim(ltrim(@Str ))
Set @i=charindex(@SeprateStr,@Str )
While @i>=1
Begin
Insert @temp values(left(@Str ,@i-1))
Set @Str =substring(@Str ,@i+1,len(@Str )-@i)
Set @i=charindex(@SeprateStr,@Str )
End
If @Str <>''
Insert @temp values(@Str )
Return
End
#2
CREATE FUNCTION [dbo].[f_get_printer]
(
@codestr varchar(20)
)
RETURNS @code TABLE
(
code varchar(10)
)
AS
begin
while(LEN(@codestr)>0)
begin
insert into @code select subString(@codestr,1,2)
set @codestr = subString(@codestr,3,LEN(@codestr))
end
RETURN
end
(
@codestr varchar(20)
)
RETURNS @code TABLE
(
code varchar(10)
)
AS
begin
while(LEN(@codestr)>0)
begin
insert into @code select subString(@codestr,1,2)
set @codestr = subString(@codestr,3,LEN(@codestr))
end
RETURN
end
#3
--建立函数
create function [dbo].[f_get_printer]
(@codestr varchar(20))
returns @code table(code varchar(10))
AS
begin
while(LEN(@codestr)>0)
begin
insert into @code select subString(@codestr,1,2)
set @codestr = subString(@codestr,3,LEN(@codestr))
end
return
end
--测试
select * from dbo.[f_get_printer]('123456789')
/*
code
----------
12
34
56
78
9
(5 row(s) affected)
*/
#1
用这个试试
/*
功能说明:传入字符串跟分割符('''SGHE00000003'',''SGHE00000004'',''SGHE00000005'''),返回一个Table
*/
ALTER function [dbo].[fnSys_SplitString]
(
---字符串分割
@Str varchar(max), --传入的字符串
@SeprateStr varchar(10)--分隔符
)
Returns @temp table(Code varchar(100)) --返回一个Table
As
Begin
Declare @i int
Set @Str=REPLACE(@str,'''','')
Set @Str =rtrim(ltrim(@Str ))
Set @i=charindex(@SeprateStr,@Str )
While @i>=1
Begin
Insert @temp values(left(@Str ,@i-1))
Set @Str =substring(@Str ,@i+1,len(@Str )-@i)
Set @i=charindex(@SeprateStr,@Str )
End
If @Str <>''
Insert @temp values(@Str )
Return
End
#2
CREATE FUNCTION [dbo].[f_get_printer]
(
@codestr varchar(20)
)
RETURNS @code TABLE
(
code varchar(10)
)
AS
begin
while(LEN(@codestr)>0)
begin
insert into @code select subString(@codestr,1,2)
set @codestr = subString(@codestr,3,LEN(@codestr))
end
RETURN
end
(
@codestr varchar(20)
)
RETURNS @code TABLE
(
code varchar(10)
)
AS
begin
while(LEN(@codestr)>0)
begin
insert into @code select subString(@codestr,1,2)
set @codestr = subString(@codestr,3,LEN(@codestr))
end
RETURN
end
#3
--建立函数
create function [dbo].[f_get_printer]
(@codestr varchar(20))
returns @code table(code varchar(10))
AS
begin
while(LEN(@codestr)>0)
begin
insert into @code select subString(@codestr,1,2)
set @codestr = subString(@codestr,3,LEN(@codestr))
end
return
end
--测试
select * from dbo.[f_get_printer]('123456789')
/*
code
----------
12
34
56
78
9
(5 row(s) affected)
*/