How to use recursive table valued function in SQL SERVER 2005

Question

I am making a split function in SQL Server 2005.

I have already done it by using a while loop .

But I am not happy with that. I want to do it using recursive function.

I have already done it in C#. Now I am plotting the same in SQL SERVER 2005. But I am getting a compilation error.

Here is my code

ALTER FUNCTION [dbo].[fnSplit2] 
(

 @OLDSTRING AS VARCHAR(100),
 @DELIMETER AS VARCHAR(1)
)

RETURNS @MYTABLE TABLE(COUNTER INT,STRINGVAL VARCHAR(100)) 
 AS
  Begin 

   DECLARE @NEWSTRING AS VARCHAR(100)
   DECLARE @POS AS INT
   DECLARE @LEN AS INT
   DECLARE @COUNTER AS INT 

   SET @NEWSTRING = '';  
   SET @LEN = LEN(@OLDSTRING)
   SET @COUNTER = 0
   SET @POS = CHARINDEX(@DELIMETER, @OLDSTRING) 

   IF(@POS > 0)

    BEGIN 

     SET @COUNTER = @COUNTER +1

     INSERT INTO @MYTABLE(COUNTER,STRINGVAL) VALUES(@COUNTER,@NEWSTRING + SUBSTRING(@OLDSTRING,0, @POS))
     SET @OLDSTRING = SUBSTRING(@OLDSTRING,0, @POS)
     fnSplit2(@OLDSTRING,@DELIMETER);

    END

   ELSE

    BEGIN
     SET @COUNTER = @COUNTER +1
     INSERT INTO @MYTABLE(COUNTER,STRINGVAL) values(@COUNTER,@OLDSTRING)
    END

 RETURN
END

The ERROR is: Msg 102, Level 15, State 1, Procedure fnSplit2, Line 38 Incorrect syntax near 'fnSplit2'.

Cannot I use a recursive table valued function in SQL SERVER ?

I searched in the google and found that Scalar valued recursive functions are possible?

Please provided the code and at the same time tell me the mistake that I am making.

Appreciate for any help!

Answer 1

Answer:

You're calling fnSplit2 in the wrong way. A table valued function is exactly that: a "table"... it goes where a "real" table would go in a FROM clause.

Comment:

If you really must split CSVs in SQL, read Erland Sommarskog's article on various ways to do it properly. Note he does not list a recursive TVF method...

Answer 2

Also, you probably need to use the schema name in the reference as well: "dbo.fnSplit2(..)". And as previously mentioned, use it as a table reference.