MS SQL Server – How to create a view from a CTE?

with cte as (
    select '2014-03-10 08:00:00' as Dates
    union all
    select '2014-05-11 14:00:00'
)
select * from cte 
join someTable on 1=1 
OPTION (MAXRECURSION 0)

The here above SQL is outputing like a charm all hours between two dates and a field retrieved from a join with another table:

2014-03-10 02:00:00    A
2014-03-10 02:00:00    B
2014-03-10 03:00:00    A
2014-03-10 03:00:00    B
...
2014-05-11 13:00:00    A
2014-05-11 13:00:00    B
2014-05-11 14:00:00    A
2014-05-11 14:00:00    B

I would like to create a view from that but I do not manage to do it. I tried several things but without success. The following is returning : Incorrect syntax near the keyword 'OPTION'.

CREATE VIEW viewName as 
with cte as (
    select '2014-03-10 08:00:00' as Dates
    union all
    select '2014-05-11 14:00:00'
)
select * from cte 
join someTable on 1=1 
OPTION (MAXRECURSION 0)

Answer

You cannot specify the MAXRECURSION option inside a view.

From http://benchmarkitconsulting.com/colin-stasiuk/2010/04/12/maxrecursion-with-a-cte-in-a-view/:

In order to make use of the MAXRECURSION option you need to first create your view without using the MAXRECURSION option:

USE AdventureWorks;
GO
CREATE VIEW vwCTE AS
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte
    JOIN  HumanResources.Employee AS e
        ON cte.ManagerID = e.EmployeeID
)
-- Notice the MAXRECURSION option is removed
SELECT EmployeeID, ManagerID, Title
FROM cte
GO

Then when you query the view include the MAXRECURSION option:

USE AdventureWorks;
GO
SELECT  EmployeeID, ManagerID, Title
FROM    vwCTE
OPTION (MAXRECURSION 2);

See also AaskashM’s answer at https://stackoverflow.com/a/7428903/195687