CODEWITHSUNDEEP
sqlsql-server-2005t-sqlrecursionuser-defined-functions
priyanka.sarkar
priyanka.sarkar

Reputation: 26518

How to use recursive table valued function in SQL SERVER 2005

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!

Upvotes: 3

Views: 8433

Answers (2)

RBarryYoung
RBarryYoung

Reputation: 56745

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.

Upvotes: 1

gbn
gbn

Reputation: 432421

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...

Upvotes: 5

Related Questions