title: How and where do I set the MAXRECURSION option?
body: <p>I have a query that exhausts the default <code>MAXRECURSION</code> limit of 100. Giving me the following error message:</p>
  
  <blockquote>
    <p>The statement terminated. The maximum recursion 100 has been exhausted before statement completion.</p>
  </blockquote>
  
  <p>I have found out that I need to raise the limit for this CTE using <code>OPTION (MAXRECURSION xxx)</code> but I don't know where to put this.  </p>
  
  <p>So far I've tried to put it next to where I define the CTE but it isn't working.  I've also tried a few different places and it's not working either.  The error I get each time is:</p>
  
  <blockquote>
    <p>Incorrect syntax near the keyword 'OPTION'.</p>
  </blockquote>
  
  <p>So where should I put the <code>OPTION (MAXRECURSION XXX)</code> command in my SQL?</p>
  
  <pre><code>with
      tab (id,start,en) AS (
                    SELECT 1, 100, 200
          UNION ALL SELECT 2, 200, 500
      ),
      cte (id,start,en) AS (
          SELECT id, start, en FROM tab
          UNION ALL
          SELECT id, start+1, en FROM cte WHERE start+1 &lt;= en
      )
  SELECT id, start
  FROM cte
  ORDER BY id
  </code></pre>