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

with cte (     select '2014-03-10 08:00:00' dates     union     select '2014-05-11 14:00:00' ) select * cte  join sometable on 1=1  option (maxrecursion 0) 

the here above sql outputing charm hours between 2 dates , field retrieved join table:

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

i create view not manage it. tried several things without success. following returning : incorrect syntax near keyword 'option'.

create view viewname  cte (     select '2014-03-10 08:00:00' dates     union     select '2014-05-11 14:00:00' ) select * cte  join sometable on 1=1  option (maxrecursion 0) 

you cannot specify maxrecursion option inside view.

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

in order make use of maxrecursion option need first create view without using maxrecursion option:

use adventureworks; go create view vwcte --creates infinite loop cte (employeeid, managerid, title) (     select employeeid, managerid, title     humanresources.employee     managerid not null   union     select cte.employeeid, cte.managerid, cte.title     cte     join  humanresources.employee e         on cte.managerid = e.employeeid ) -- notice maxrecursion option removed select employeeid, managerid, title cte go 

then when query view include maxrecursion option:

 use adventureworks; go select  employeeid, managerid, title    vwcte option (maxrecursion 2);

see aaskashm's answer @ https://stackoverflow.com/a/7428903/195687