Create view with recursive CTE

Question

Trying to define a recursive cte inside a view.

However, there is an error: Incorrect syntax near the keyword 'OPTION'.

If I remove the OPTION(maxrecursion 100) then there is no error.

Is there any solutions to add this OPTION(maxrecursion 100) without error message?

Answer 1

Hi @Lora

You cannot specify the maxrecursion option inside a view.

You could first create your view without the MAXRECURSION option, and then query the view include the MAXRECURSION option like this:

SELECT  columns
FROM    your_view
OPTION (maxrecursion 100);

Best regards,

Cosmog Hong