So a minor “gotcha” here to start your day. If you’ve worked with CTEs before you’ve probably also worked with the MAXRECURSION option which specifies how many recursion levels deep to go before cancelling a statement.
(Code below borrowed from: http://msdn.microsoft.com/en-us/library/ms175972.aspx)
USE AdventureWorks;
GO
--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
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GONow if you wanted to create a view based on your CTE and SELECT statement and included your MAXRECURSION option you’d get an error message
Msg 156, Level 15, State 1, Procedure numbers, Line 15
Incorrect syntax near the keyword ‘OPTION’.
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
GOThen when you query the view include the MAXRECURSION option:
USE AdventureWorks; GO SELECT EmployeeID, ManagerID, Title FROM vwCTE OPTION (MAXRECURSION 2);
Nothing too deep for this morning but whenever I have a “so THAT’s how it works” moment I like to share 
Enjoy!!
Thank you very much ! I have been searching from a long time how to do this. It was simple, but not explained as well as here !
Thanks for this article, but I am having trouble using it.
I want to create a view that calls another view. The outer view would contain the OPTION (MAXRECURSION 500) statement, to apply it to the inner view. But I get the error message “Query hints cannot be used in this query type”.
Outer view:
SELECT * FROM dbo.vw_FEDEX_OUT_Inner OPTION (MAXRECURSION 500)
Any idea how to use MAXRECURSION in a view?
Thanks,
Paul