Getting syntax error while using table variable in table value function in SQL Server 2012

Question

I am trying to use table variable with table value function but I am getting a syntax error. Please help me to solve this.

Here is the code:

Create FUNCTION [dbo].[SplitStrings1]
(@List       NVARCHAR(MAX),
@Delimiter  NVARCHAR(255))
RETURNS @Results TABLE(Col1 int)
AS
BEGIN
    declare @tblHelping table (Col1 int);
    declare @i int
    declare @rows_to_insert int

    set @i = 1
    set @rows_to_insert = 1000

    while @i < @rows_to_insert
    begin
        INSERT INTO @tblHelping VALUES (@i)
        set @i = @i + 1
    end 

    (SELECT 
         Number = ROW_NUMBER() OVER (ORDER BY Number),
         Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number, 
         CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
    FROM 
        (SELECT * FROM @tblHelping) AS n(Number)
    WHERE 
        Number <= CONVERT(INT, LEN(@List))
        AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter) AS y) 
END

I am getting this error

A RETURN statement with a return value cannot be used in this context.

Is that your complete code? I don't see a RETURN statement. — Gordon Linoff
– Gordon Linoff, Commented Nov 6, 2015 at 15:57
This appears to be some sort of attempt at splitting delimited strings. The while loop approach is the absolute worst possible way of doing this from a performance perspective. Using this will cripple your queries. Check out this article for a number of far better alternatives. sqlperformance.com/2012/07/t-sql-queries/split-strings — Sean Lange
– Sean Lange, Commented Nov 6, 2015 at 16:30
if you really get this error message, just remove the number after the RETURN-Statement. So if you have RETURN 0 just write RETURN — CPMunich
– CPMunich, Commented Nov 6, 2015 at 16:33
Select statements included within a function cannot return data to a client. Where is insert into @Results? — Roman Marusyk
– Roman Marusyk, Commented Nov 6, 2015 at 16:47
@GordonLinoff: Yes this is the complete code. If I try to add Return statement then I got another error that "A RETURN statement with a return value cannot be used in this context." — user999896
– user999896, Commented Nov 7, 2015 at 8:36

Community · Accepted Answer · 2017-05-23 12:15:43Z

If you want to use your function then working version below. But there is better solution, please see:

Split strings the right way – or the next best way as recomended by
@SeanLange
How to split string using delimiter char using T-SQL?
How do I split a string so I can access item x?

.

CREATE FUNCTION [dbo].[SplitStrings1] 
( @List       NVARCHAR(MAX)
, @Delimiter  NVARCHAR(255))
RETURNS @Results TABLE
(Number INT, Item NVARCHAR(MAX) )
AS
BEGIN

  declare @tblHelping table (Col1 int);
  declare @i int
  declare @rows_to_insert int

  SET @i = 1
  SET @rows_to_insert = 1000

  while @i < @rows_to_insert
  begin
    INSERT INTO @tblHelping VALUES (@i)
    set @i = @i + 1
  end 

   insert into @Results
   SELECT Number = ROW_NUMBER() OVER (ORDER BY Number)
   ,      Item 
   FROM (SELECT Number
         ,      Item = LTRIM(RTRIM(SUBSTRING(@List, Number,CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
         FROM  (SELECT * FROM @tblHelping) AS n(Number)
         WHERE  Number <= CONVERT(INT, LEN(@List))
           AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter) AS y
  RETURN 
END

Or try something like that:

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

Please NO!!! This is the absolute worst possible way to write a splitter. Loops like this are just awful for performance. There are far better ways to write a splitter than using a while loop. sqlperformance.com/2012/07/t-sql-queries/split-strings
@SeanLange Thanks a lot. It is a very interesting information. I'll use it in my database

Collectives™ on Stack Overflow

Getting syntax error while using table variable in table value function in SQL Server 2012

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related