title: SQL Server : the maximum recursion 100 has been exhausted before statement completion
body: <p>I have a query that is returning an error with the maximum levels of recursion exceeded. </p>
  
  <p>I know how to fix this by adding <code>OPTION (maxrecursion 0)</code> to the query however, I have tried adding this at various places in the query and I cant find where to put it where the syntax is valid.</p>
  
  <p>Can anyone give me any pointers as to where in my view the query hint needs to be inserted?</p>
  
  <pre><code>/****** Object:  View [dbo].[SiconCFMContractLinesDetailByDayView]    Script Date: 16/12/2016 12:02:35 ******/
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  CREATE VIEW [dbo].[SiconCFMContractLinesDetailByDayView]
  AS
  WITH dateRange as
  (
  SELECT      [Date] = DATEADD(dd, 1, DATEADD(dd, -1,[SiconCFMContractLinesOutstandingView].[NextDueDate])),
  [Frequency] = [SiconCFMContractLinesOutstandingView].[FrequencyValue],
  [EndDate] = DATEADD(yy,5, [SiconCFMContractLinesOutstandingView].[NextDueDate]),
  [SiconCFMContractLinesOutstandingView].[SiconContractLineID]
  FROM        [SiconCFMContractLinesOutstandingView]
  WHERE       DATEADD(mm, [SiconCFMContractLinesOutstandingView].[FrequencyValue], [SiconCFMContractLinesOutstandingView].[NextDueDate]) &lt; DATEADD(mm, [SiconCFMContractLinesOutstandingView].[FrequencyValue], DATEADD(yy,5, [SiconCFMContractLinesOutstandingView].[NextDueDate]))
  UNION ALL
  SELECT      DATEADD(mm, [Frequency], [Date]) [Date],
  [Frequency],
  [EndDate],
  [SiconContractLineID]
  FROM        dateRange
  WHERE       DATEADD(mm, [Frequency], [Date]) &lt; DATEADD(mm, [Frequency],[EndDate])
  
  )
  
  SELECT
  (
  SELECT CASE
  WHEN dbo.fnSiconCFMGetSettingValue('UseAverageTimeToPay') = 'True'
  THEN
  CASE [SiconCFMSLCustomerAverageTimeToPayView].[AvgTimeToPayDateLastUpdated]
  WHEN NULL THEN dbo.fnSiconCFMDateByPaymentTerms([SLCustomerAccount].[SYSPaymentTermsBasisID], [SLCustomerAccount].[PaymentTermsInDays], dateRange.[Date])
  ELSE DATEADD([DD],[SiconCFMSLCustomerAverageTimeToPayView].[Days],dateRange.[Date])
  END
  ELSE dbo.fnSiconCFMDateByPaymentTerms([SLCustomerAccount].[SYSPaymentTermsBasisID], [SLCustomerAccount].[PaymentTermsInDays], dateRange.[Date])
  END
  )
  AS [DueDate],
  [StockItem].[Name] AS [Title],
  [SiconCFMContractLinesOutstandingView].[Description] AS [Description],
  [SiconCFMContractLinesOutstandingView].[UnitBillCoverPriceIncDisc] AS [Value],
  [SiconCFMContractLinesOutstandingView].[SiconContractID],
  [SiconCFMContractLinesOutstandingView].[SiconContractLineID],
  [SiconCFMContractLinesOutstandingView].[SiconContractLineID] AS [ForecastDateForeignID],
  'SiconContractLine' AS [ForecastDateSource],
  (
  SELECT
  CASE WHEN EXISTS
  (
  SELECT [SiconCFMMemo].[SiconCFMMemoID]
  FROM   [SiconCFMMemo]
  WHERE  [SiconCFMMemo].[Deleted]=0
  AND    [SiconCFMMemo].[IsActive]=1
  AND    [SiconCFMMemo].[MemoSource]='SiconContractLine'
  AND    [SiconCFMMemo].[MemoForeignID]=[SiconCFMContractLinesOutstandingView].[SiconContractLineID]
  )
  THEN 1
  ELSE 0
  END
  ) AS [HasMemos]
  FROM            dateRange 
  INNER JOIN      [SiconCFMContractLinesOutstandingView]
  ON              dateRange.[SiconContractLineID]
  =               [SiconCFMContractLinesOutstandingView].[siconContractLineID]
  INNER JOIN      [StockItem]
  ON              [StockItem].[ItemID]
  =               [SiconCFMContractLinesOutstandingView].[ItemID]
  INNER JOIN      [SLCustomerAccount]
  ON              [SLCustomerAccount].[SLCustomerAccountID]
  =               [SiconCFMContractLinesOutstandingView].[SLCustomerAccountID]
  INNER JOIN      [SiconCFMSLCustomerAverageTimeToPayView]
  ON              [SiconCFMSLCustomerAverageTimeToPayView].[SLCustomerAccountID]
  =               [SLCustomerAccount].[SLCustomerAccountID]
  GO
  </code></pre>
  
  <p>as suggested I have added <strong>OPTION (maxrecursion 0)</strong> to just above the last GO statement, however when within the create view statement it is giving a syntax error. If I run the query on its own, outside of a create view statement it works</p>