Problem with functions return statement

[xlncsql]

Hi I am beginner to sqlserver and facinf a problem with functions can I get some help on it.
Here is my programm

create function fun_split
(
@string varchar(20)
)
RETURN char(5)
begin
declare @b int,@c char(5)
set @string = replace('m1,m2,m3',',','.')
set @b=0
while(@b<4)
begin
@c=parsename(@string,@b)
set @b=@b+1
return (@c)
end
end

The error am facing

Msg 156, Level 15, State 1, Procedure fun_split, Line 9
Incorrect syntax near the keyword 'RETURN'.
Msg 178, Level 15, State 1, Procedure fun_split, Line 9
A RETURN statement with a return value cannot be used in this context.
Msg 102, Level 15, State 1, Procedure fun_split, Line 16
Incorrect syntax near '@c'.
Msg 178, Level 15, State 1, Procedure fun_split, Line 18
A RETURN statement with a return value cannot be used in this context.

 

[gmmastros]

What is this function supposed to do? Can you describe it in words, and also show some sample inputs and expected outputs.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

There are a couple of obvious syntax problems.

[tt][blue]
create function fun_split
(
@string varchar(20)
)
RETURN[!]S[/!] char(5)
begin
declare @b int,@c char(5)
set @string = replace('m1,m2,m3',',','.')
set @b=0
while(@b<4)
begin
[!]Set [/!]@c=parsename(@string,@b)
set @b=@b+1
end

[!]return (@c)[/!]
end
[/blue][/tt]

For scalar functions, you need to identify the output data type using the RETURNS keyword (with the S).

To assign a value to a variable, you need to use the SET keyword.

Return MUST be the last statement in a function.




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[xlncsql]

This function is splitting the string 'm1,m2,m3' into
m1
m2
m3
I made few more correction can you check this


create function fun_split
(
@string varchar(20)
)
RETURN char(5)
begin
declare @b int,@c varchar(5), @d varchar(5)
set @d = replace('@string',',','.')
set @b=0
while(@b<4)
begin
set @c=parsename(@d,@b)
set @b=@b+1
return @c
end
end

 

[markros]

There are many better functions to do the same available.

See this great article on this topic

http://www.sommarskog.se/arrays-in-sql-2008.html

If you're using SQL Server 2005 and up, then here is the one I use

USE [AllTests]
GO

/****** Object:  UserDefinedFunction [dbo].[fnSplit]    Script Date: 03/04/2010 22:20:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- Test query
CREATE FUNCTION [dbo].[fnSplit]
(@list  VARCHAR(8000),
 @delim CHAR(1) = ','
) RETURNS TABLE AS
RETURN
   WITH csvtbl(START, stop) AS (
     SELECT START = 1,
            stop = CHARINDEX(@delim COLLATE Slovenian_BIN2, @list + @delim)
     UNION ALL
     SELECT START = stop + 1,
            stop = CHARINDEX(@delim COLLATE Slovenian_BIN2,
                             @list + @delim, stop + 1)
     FROM   csvtbl
     WHERE  stop > 0
  )
  SELECT LTRIM(RTRIM(SUBSTRING(@list, START,
                      CASE WHEN stop > 0 THEN stop - START ELSE 0 END)))
         AS VALUE
  FROM   csvtbl
  WHERE  stop > 0
GO

PluralSight Learning Library

 

[xlncsql]

I did make the changes how ever the function is created but its not giving me expected results I am guessing its because of return not in the loop if can you explane me it just returns NULL as of now

 

[xlncsql]

In the above example instead of returning a table cant we return a variable can some one explane it