Incorrect syntax near the keyword 'OPTION'. When using CTE's inside a UDF.

It is useful to use common table expressions (CTE) to perform recursive calls.

Normally recursion is limited by default to 100 recursions, this is to stop infinite loops.

You can normally override the default using the MAXRECURSION query hint. e.g.


OPTION (MAXRECURSION 200); -- overrides with 200 recursions, but will break inside UDFs


Unfortunately if you set this inside a UDF, you will get the error: Incorrect syntax near the keyword 'OPTION'. This is a known issue mentioned in microsoft connect.

A simple solution

Fortunately if you have control of the outer SQL, it is simply a case of setting MAXREECURSION on the SQL calling the UDF e.g

SELECT X FROM YourUDF(Parameter) OPTION(MAXRECURSION 0)

Of course this solution is only applicable in some situations, some of you really need to be able to configure the recursion within the UDF. But when I came across this, it helped me.