在语句完成之前，最大递归100已用尽。 [英] The maximum recursion 100 has been exhausted before statement completion.

问题描述

以下是我的存储过程，它有很多连接。它成功创建了...但是当我要执行它时，它会给我以下错误...



声明终止。在语句完成之前，最大递归100已经用尽。

请解决我的问题。

Following is my Stored Procedure which has many joins. It created successfully... But When I am going execute it , it gives me following error...

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
Please solve my problem.

USE [Db_GoldenCoin]
GO
/****** Object:  StoredProcedure [dbo].[getDownLineWithPaging]    Script Date: 04/06/2016 23:28:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[getDownLineWithPaging]
@SearchTerm varchar(100)='',
@PageIndex int=1,
@PageSize int=10,
@mid varchar(20),
@RecordCount int Output
AS
Begin
set NOCOUNT ON;
with DownLineCTE as
(
SELECT tbl_MemberTransaction.MemberId, tbl_MemberTransaction.PlacemMentID
from tbl_MemberTransaction
where tbl_MemberTransaction.PlacemMentID=@mid

UNION ALL

SELECT tbl_MemberTransaction.MemberId, tbl_MemberTransaction.PlacemMentID
FROM tbl_MemberTransaction
JOIN DownLineCTE
ON tbl_MemberTransaction.PlacemMentID=DownLineCTE.MemberId
)

SELECT ROW_NUMBER() OVER
(
ORDER BY m.MemberId ASC
)AS RowNumber,
m.MemberId, MemberName=m.MemberFirstName+' '+ m.MemberLastName,t.SponceID,t.PlacemMentID, m.MobileNumber,pc.PkgName,JoiningDate=CONVERT(VARCHAR(10), m.JoiningDate, 103) 
INTO #Results 
FROM DownLineCTE as ct
LEFT join tbl_MemberMaster as m
ON m.MemberId=ct.MemberId
LEFT JOIN tbl_MemberTransaction as t
ON m.MemberId= t.MemberId
LEFT JOIN tbl_PINDetails as pd
ON m.SecurityPIN = pd.PIN
LEFT JOIN tbl_PinTransferTbl as pt
ON pd.TransferId= pt.PinTransferId
LEFT JOIN tbl_PackageMaster pc
ON pt.PackageId= pc.PkgId
WHERE m.MemberId LIKE @SearchTerm +'%' OR m.MemberFirstName LIKE @SearchTerm +'%'
OR m.MemberLastName LIKE @SearchTerm + '%' OR m.MobileNumber LIKE @SearchTerm +'%'
OR t.SponceID LIKE @SearchTerm + '%' OR t.PlacemMentID LIKE @SearchTerm + '%'

SELECT @RecordCount=COUNT(*)
  FROM #Results
          
      SELECT * FROM #Results 
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
    
      DROP TABLE #Results

      
END

推荐答案

错误确实有点令人困惑，因为你没有真正写过一个递归......但你使用的是CTE，那就是实际上是一种递归（或在le这是以这种方式实现的。

如果没有定义，一个简单的CTE最多只能有100条记录...

使用MAXRECURSION将其设置得更大或更大（ 0）...

使用common_table_expression（Transact-SQL） [ ^ ]

The error indeed can be a bit confusing, as you didn't really wrote a recursion...But you use CTE and that's actually a kind of recursion (or at least it is implemented that way).
If not defined otherwise a simple CTE can have up to 100 records only...
Use MAXRECURSION to set it larger or infinite (0)...
WITH common_table_expression (Transact-SQL)[^]

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