CTE in a View
It is possible to use Common Table Expression in a View. But newbies wonder why the following is not possible
create
view numbers
as
with numbers(n) as
(
select 1 as n union all select n+1 from numbers where n<10000
)
select
n from numbers option(maxrecursion 0)
Which when created results to the error
Msg 156, Level 15, State 1, Procedure numbers, Line 15
Incorrect syntax near the keyword 'option'.
The correct way of doing it is to create a view without option(maxrecursion 0) and use it when querying a view
create
view numbers
as
with numbers(n) as
(
select 1 as n union all select n+1 from numbers where n<10000
) select n from numbers
GO
select
* from numbers option (maxrecursion 0)