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

Question

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)

Solution 1

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

Solution 2

If you have more than 100 expected results, and want to avoid having to add the OPTION statement to your VIEW calls, try executing the CTE query - including the OPTION clause - in an OPENQUERY statement within your VIEW.

In your example, it would probably look something like this:

USE AdventureWorks;
GO

CREATE VIEW vwCTE AS
select * from OPENQUERY([YourDatabaseServer], '
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
  SELECT EmployeeID, ManagerID, Title
    FROM AdventureWorks.HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
  SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte
    JOIN  AdventureWorks.HumanResources.Employee AS e
      ON cte.ManagerID = e.EmployeeID
)
-- Notice the MAXRECURSION option is removed
SELECT EmployeeID, ManagerID, Title
  FROM cte
  OPTION (MAXRECURSION 0)
' ) x
GO

Notice that you must fully-qualify object references, i.e. the database and user specifications must prefix the object (table, view, sproc, or function) references.

Sure, it's a little ugly, but gets the job done nicely, and avoids having to add that pesky OPTION clause.