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, Nov 06 '15 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. http://sqlperformance.com/2012/07/t-sql-queries/split-strings — Sean Lange, Nov 06 '15 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, Nov 06 '15 at 16:33
Select statements included within a function cannot return data to a client. Where is insert into @Results? — Roman Marusyk, Nov 06 '15 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, Nov 07 '15 at 08:36
@Sean Lange: You are right according to the performance point of view. But at the moment, I am fighting with syntax error. Can you please help me on that? — user999896, Nov 07 '15 at 08:37
@MegaTron: Can you please help me to fix the syntax error. Because I never used Table variable with in table value function. — user999896, Nov 07 '15 at 08:39
@CPMunich: As you can see in the code I don't have any number after Return statement. — user999896, Nov 07 '15 at 08:41
@user999896 There is no RETURN-Statement in your code above. The SELECT-Statement after your INSERT-INTO Loop inside the function doesn't make sense either. See the Syntax of functions here: https://msdn.microsoft.com/en-US/library/ms186755(v=sql.110).aspx — CPMunich, Nov 09 '15 at 09:11

score 0 · Accepted Answer · edited May 23 '17 at 12:15

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. http://sqlperformance.com/2012/07/t-sql-queries/split-strings — Sean Lange, Nov 09 '15 at 14:30
@SeanLange Thanks a lot. It is a very interesting information. I'll use it in my database — Roman Marusyk, Nov 09 '15 at 15:11

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

1 Answers1