SQL Server:在说明完成之前，最大递归100已经用尽

Question

我有一个查询，该查询正在返回一个错误，最大递归水平超过了。

我知道如何通过在查询中添加OPTION (maxrecursion 0)来解决此问题，但是，我尝试在查询的各个位置添加此问题，但我找不到将其放在语法有效的位置。

。

任何人都可以给我任何关于我认为需要插入查询提示的指示吗？

/****** 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]) < 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]) < 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

按照建议的我添加了 option（maxRecursion 0） 在上一个GO语句上方，但是当创建视图语句中时，它给出了语法错误。如果我自己运行查询，则在创建视图语句之外，它可以工作

Answer 1

通常，在使用反式cte的选择语句的结尾。
但是，在视图内行不通的视图。快速搜索让我进入了这篇文章 - 解释了正确的方法。

事实证明，您无法在视图内使用查询提示，但是您可以并且应该在调用视图的查询中使用它。

样本表：

CREATE TABLE T
(
    id int,
    parent int
)
INSERT INTO T VALUES (1, NULL), (2, 1), (3, 1), (4, 2), (5, 2), (6, 3), (7, 4), (8, 5);
GO

创建视图：

CREATE VIEW V
AS
WITH CTE AS
(
    SELECT id, parent
    FROM T 
    WHERE parent IS NULL
    UNION ALL
    SELECT t.id, t.parent
    FROM T
    INNER JOIN CTE ON t.parent = cte.id
)
SELECT *
FROM CTE
GO

执行视图：

SELECT *
FROM V
OPTION (MAXRECURSION 2);

Answer 2

有一个非常不错的脚本，我用来递归检查外国钥匙，然后由Ann Lewkowicz

但是，我曾经有一个数据库，该数据库有很多约束，包括某些表中的外键约束。

在运行上面的脚本时，我收到以下错误：

该声明终止。最大递归32767已经 在声明完成之前筋疲力尽。

然后，我使用top限制了脚本的输出，并且一切都很好

 select top 100 *
 from   recurseWithFields 
--OPTION (MAXRECURSION 32767);