CODEWITHSUNDEEP
sqlsql-server
Parashuram
Parashuram

Reputation: 1569

SQL Server Option tag usage

I have below function.

ALTER FUNCTION [dbo].[fnSplit] (@s VARCHAR(8000))
RETURNS table
AS
RETURN (
    WITH Pieces(start, stop) AS (
      SELECT 1, CHARINDEX(',', @s)
      UNION ALL
      SELECT stop + 1, CHARINDEX(',', @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 8000 END) AS s
    FROM Pieces
  )

If i run this function with very big length string, i am getting the error "The maximum recursion 100 has been exhausted before statement completion". I am not able to apply "option (maxrecursion 0)" as it is giving me error saying "Syntax error near Option".

Can anyone tell me what is the issue with this please ?

Thanks in advance.

Upvotes: 0

Views: 56

Answers (2)

David Rushton
David Rushton

Reputation: 5030

You cannot add the OPTION clause to a function definition. But you can use it when calling the function.

This example using the follow UDF, complete with recursive CTE.

CREATE FUNCTION dbo.fnTEST (@Limit INT)
RETURNS TABLE
AS
RETURN
    (
        /* Returns a table counting 1 to @Limit
         * using recursion.
         */
        WITH x AS
            (
                    SELECT
                        1 AS n

                UNION ALL

                    SELECT
                        n + 1
                    FROM 
                        x
                    WHERE
                        n < @Limit
            )
        SELECT
            *
        FROM
            x    
    )
GO

Any @Limit past 100 with returns an error unless you include the MAXRECURSION table hint from the calling statement.

How to Call

SELECT
    *
FROM
    dbo.fnTEST(105)
OPTION
    (MAXRECURSION 200)        
;

EDIT: As others have pointed out there are more efficient solutions to this problem. In this case you would probably be better off refactoring your code, see comment from @Lad2025 in OP, which includes a handy demo.

EDIT 2: @Devart has posted an answer that shows you can include the OPTION clause, if you first build your table within a VAR and then return it.

Upvotes: 1

Devart
Devart

Reputation: 122002

CREATE FUNCTION dbo.fnSplit
(
    @s VARCHAR(8000)
)
RETURNS @ret TABLE (val NVARCHAR(4000))
AS BEGIN

    ;WITH Pieces(start, [stop]) AS (
      SELECT 1, CHARINDEX(',', @s)
      UNION ALL
      SELECT [stop] + 1, CHARINDEX(',', @s, [stop] + 1)
      FROM Pieces
      WHERE [stop] > 0
    )
    INSERT INTO @ret (val)
    SELECT SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 8000 END) AS s
    FROM Pieces
    OPTION(MAXRECURSION 0)

    RETURN

END
GO

Upvotes: 0

Related Questions