获取错误“语句终止。在语句完成之前，最大递归100已用尽。 [英] Getting error "the statement terminated. The maximum recursion 100 has been exhausted before statement completion.

问题描述

我使用下面这个函数来获取总公司分支的父子代码。但错误的是，这两个值已更新为NULL，现在我调用此函数时，得到错误语句已终止。最大递归100在语句完成之前已用尽。 。请指导

Im using this below function to fetch the parent child code for a branch with that of head office. But by mistaken, these two value got updated to NULL and now while i call this function , getting error "The statement terminated. The maximum recursion 100 has been exhausted before statement completion." .Please guide

CREATE FUNCTION [dbo].[FUN_GETCHILD_CODE](@LEVEL_NO   VARCHAR(20),@PARENT_CODE VARCHAR(20), @CHILD_CODE  VARCHAR(20))       
RETURNS @Values TABLE( CHILD_CODE VARCHAR(20),CHILD_DESC VARCHAR(200),PARENT_CODE VARCHAR(20),LEVEL_NO VARCHAR(20),LEVEL_CODE VARCHAR(20))  AS      
BEGIN     
   
IF @LEVEL_NO = 'NULL'   
SET @LEVEL_NO='0'  
  
IF @PARENT_CODE = 'NULL'   
SET @PARENT_CODE='0'  
  
IF @CHILD_CODE = 'NULL'   
SET @CHILD_CODE='0';  
   
WITH LEVEL_PARENT(CHILD_CODE, CHILD_DESC, PARENT_CODE,LEVEL_CODE) AS    
(    
       
          SELECT CHILD_CODE, CHILD_DESC, PARENT_CODE,LEVEL_CODE    
          FROM TB_LEVELS_PARENT_CHILD     
          WHERE     
   PARENT_CODE = @PARENT_CODE OR CHILD_CODE = @CHILD_CODE    
          UNION ALL    
          SELECT E.CHILD_CODE, E.CHILD_DESC, E.PARENT_CODE,E.LEVEL_CODE    
          FROM TB_LEVELS_PARENT_CHILD E     
          INNER JOIN LEVEL_PARENT P ON P.CHILD_CODE = E.PARENT_CODE    
)    
INSERT INTO @Values ( CHILD_CODE,CHILD_DESC,PARENT_CODE,LEVEL_NO,LEVEL_CODE)    
SELECT A.CHILD_CODE, A.CHILD_DESC, A.PARENT_CODE,B.LEVEL_NO,A.LEVEL_CODE     
FROM LEVEL_PARENT A,TB_LEVEL_MASTER B    
WHERE     
A.LEVEL_CODE = B.LEVEL_CODE AND    
B.LEVEL_NO = @LEVEL_NO     
ORDER BY LEVEL_NO DESC,PARENT_CODE DESC    
RETURN    
END  

< b>我有什么ied：

What I have tried:

SELECT DISTINCT CHILD_CODE CHILD_CODE FROM FUN_GETCHILD_CODE('1','HO','HO')

推荐答案

请，参考这些：

声明终止。在语句完成之前，最大递归100已用尽。 | SqlHints.com [ ^ ]

递归CTE&＃8211;最大递归100已经用尽了SQL与Manoj [ ^ ]



您将找到如何避免此类错误的说明。此时你必须编写适当的 WHERE 语句，它可以帮助你停止循环，或者你可以使用 OPTION简单地将循环限制限制为特定的数字（MAXRECURSION YourNumberHere）在声明结束时。



更多来自MSDN：

使用common_table_expression（Transact-SQL） [ ^ ]

使用公用表表达式 [ ^ ]

使用公用表表达式的递归查询 [ ^ ]

优化递归CTE查询| SQL Server客户咨询团队 [ ^ ]

Please, refer these:
The statement terminated. The maximum recursion 100 has been exhausted before statement completion. | SqlHints.com[^]
Recursive CTE &#8211; maximum recursion 100 has been exhausted | SQL with Manoj[^]

There you'll find instruction how to avoid such of errors. At this moment you have to write proper WHERE statment which can help you to stop loop or you can simply restrict the limit of loops to specific number using OPTION (MAXRECURSION YourNumberHere) at the end of statement.

More at MSDN:
WITH common_table_expression (Transact-SQL)[^]
Using Common Table Expressions[^]
Recursive Queries Using Common Table Expressions[^]
Optimize Recursive CTE Query | SQL Server Customer Advisory Team[^]

引用：

这两个值已更新到NULL

these two value got updated to NULL

用NULL更新的每一行现在都是它自己的父和子。

这是一个无限循环。

恢复父值和子值或更改逻辑查询。

Each row updated with NULL is now its own parent and child.
That is an infinite loop.
restore parent and child values or change the logic of the query.

这篇关于获取错误“语句终止。在语句完成之前，最大递归100已用尽。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！