Incorrect syntax near the keyword 'OPTION'. CREATE Views with CTE 's

Posted: 2008-10-28 in .Net

Arg Microsoft you have done it to me again!!!
I am a massive fan of cte’s but i recent encounted a situation that set me back.

We just released an application that has a dashboard the first page shows an overview including a table with the amount of items processed for the past 7 days. The problem was that this table would show no lines if nothing was processed during that day.
I solved this using 2 cte’s, one with the needed precalculations, the other creating a listing of days. The solution was perfect until I moved it to the “create view” ready for release.
The solution requires around 7500 recursive steps, over the 100 default, which was controlled using the option tag max recursion.

When you use the keyword option in a view it returns
Incorrect syntax near the keyword ‘OPTION’.

After much searching I find that this is by design and that Views and some other SQL functions can NOT have the option tag in the creation section but instead it is up to the caller to specifier the OPTION.

To me this is backwards.
Why would I want to pass onto the user/caller information about the view internals and that it is using a recursive statement. Worse to this matter it is now up to them to control the recursion.
My guess is that any person who wanted to use this view would simply set the MAXRECURSION to 0, breaking the one reason that MAXRECURSION exist.

There is no work around to this. To snow ball this the lack of function documentation support (like what VS does to C# function code comments) it is imposible to give the user any decent feedback or hint as to what the maxrecursion should be set too.

So back to non recursive TSQL solutions, yeah! 😦 or in my case, leave it as a unresolve bug and pass it to the C# guys to fix. There goes the coupling and cohesion.


  1. Adrian says:

    Have you tried to encapsulate the logic in a multi-statement table-valued function?
    It seems the OPTION has a problem with single-statement objects like views or Inline table-valued functions.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s