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)

Published Saturday, July 11, 2009 11:51 AM by Madhivanan
Filed under: , , ,

Comments

# Morning News 2009-07-14 &laquo; C# Hacker &#8211; The Rambling Coder

Pingback from  Morning News 2009-07-14 &laquo;  C# Hacker &#8211; The Rambling Coder

# DevAdmin &raquo; Blog Archive &raquo; SQL Server: creazione di una vista calendario

Pingback from  DevAdmin  &raquo; Blog Archive   &raquo; SQL Server: creazione di una vista calendario

# SQL Server: creazione di una vista calendario | DevAdmin Blog

Pingback from  SQL Server: creazione di una vista calendario | DevAdmin Blog