Rotating Header Image

MAXRECURSION with a CTE in a View

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);
GO

 Now 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
GO

Then 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!!

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

6 Comments

  1. B BERTELLE says:

    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 !

  2. Paul says:

    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

  3. […] An easy thing to do is to move the ‘OPTION (MAXRECURSION 0)’ from inside the View to the select query that’s using the View: http://benchmarkitconsulting.com/colin-stasiuk/2010/04/12/maxrecursion-with-a-cte-in-a-view/ […]

  4. Zak Willis says:

    Yes – we all know about specifying the option maxrecursion in a view but the point is that views represent an abstraction or encapsulation so that there would be no simple way of knowing that a view was based upon a CTE or not.

    it would mean that every view would need the maxrecursion thrown at it just in case somebody had based the view upon a common table expression.

    Doesn’t this seem preposterous?

    create view v_test
    as select 1 fielda
    go
    select * from v_test
    option (maxrecursion 10000)

  5. Going back to a physical keyboard this past week turned out to be cumbersome. Yes, I liked that while in another program, the BlackBerry gave me a little banner up top announcing the sender of a new email.

  6. Lisa O. says:

    “Nothing too deep for this morning but whenever I have a “so THAT’s how it works” moment I like to share”

    And I for one appreciate it, six years later. Saved me some thrashing around on a short deadline, so thanks!

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.