过程 超过最大存储过程、函数、触发器或视图嵌套级别（限制 32）

Question

存储过程在以下位置失败，谢谢您的帮助。

--Insert MSOrg Information    

DECLARE @PersonnelNumber int,    
 @MSOrg   varchar(255)    

DECLARE csr CURSOR FAST_FORWARD FOR    
 SELECT PersonnelNumber FROM Person    

OPEN csr    

FETCH NEXT FROM csr     
 INTO @PersonnelNumber    

WHILE @@FETCH_STATUS = 0    
BEGIN    

 EXEC GetMSOrg @PersonnelNumber, @MSOrg out    

 INSERT INTO PersonSubject (    
   PersonnelNumber    
   ,SubjectID    
   ,SubjectValue    
   ,Created  
   ,Updated  
  )    
  SELECT @PersonnelNumber    
   ,SubjectID    
   ,@MSOrg    
   ,getDate()  
   ,getDate()  
  FROM Subject    
  WHERE DisplayName = 'MS Org'    

 FETCH NEXT FROM csr     
  INTO @PersonnelNumber    
END    

CLOSE csr    
DEALLOCATE csr    

下面是存储的 prc 定义 GetMSOrg，在第三个条件下失败

CREATE  PROCEDURE [dbo].[GetMSOrg]
(
    @PersonnelNumber    int
    ,@OrgTerm       varchar(200)    out
)
AS
DECLARE @MDRTermID          int
        ,@ReportsToPersonnelNbr int
--Check to see if we have reached the top of the chart
SELECT  @ReportsToPersonnelNbr = ReportsToPersonnelNbr
FROM        ReportsTo
WHERE   PersonnelNumber = @PersonnelNumber
IF (@ReportsToPersonnelNbr IS NULL) --Reached the Top of the Org Ladder
BEGIN
    SET @OrgTerm = 'Non-standard rollup'
END
ELSE IF (@PersonnelNumber IN (SELECT PersonnelNumber FROM OrgTermMap))
BEGIN
    SELECT  @OrgTerm = s.Term
    FROM    OrgTermMap tm
        JOIN Taxonomy..StaticHierarchy s ON tm.OrgTermID = s.TermID
    WHERE   tm.PersonnelNumber = @PersonnelNumber
END
ELSE
BEGIN
    SELECT  @MDRTermID = tm.OrgTermID
    FROM        ReportsTo r
        JOIN OrgTermMap tm ON r.ReportsToPersonnelNbr = tm.PersonnelNumber
    WHERE   r.PersonnelNumber = @PersonnelNumber
    IF (@MDRTermID IS NULL)
    BEGIN
        EXEC GetMSOrg @ReportsToPersonnelNbr, @OrgTerm out
    END
    ELSE
    BEGIN
        SELECT  @OrgTerm = Term
        FROM    Taxonomy..StaticHierarchy
        WHERE VocabID = 118
            AND TermID = @MDRTermID
    END
END

GO

Answer 1

每次调用 EXEC GetMSOrg 都会沿着查询继续进行，

SELECT  @ReportsToPersonnelNbr = ReportsToPersonnelNbr
FROM        ReportsTo
WHERE   PersonnelNumber = @PersonnelNumber

并且当 PersonnelNumber 不再向任何人报告时，有一个退出子句。

因此，唯一合乎逻辑的结论是，你有一个周期性的人->报告对象->..->人。这甚至可以在单个记录上。

此查询将在报告到记录中查找循环：

declare @PersonnelNumber int
set @PersonnelNumber = 10

;with CTE as (
select 1 Level,
       convert(varchar(max),@PersonnelNumber) Seed,
       '>>' + convert(varchar(max),@PersonnelNumber) + '>>' Path
union all
SELECT Level+1,
       convert(varchar(max),ReportsToPersonnelNbr),
       Path + convert(varchar(max),ReportsToPersonnelNbr) + '>>'
FROM   ReportsTo
join CTE on CTE.Seed = ReportsTo.PersonnelNumber
where  Level < 40
)
select *
from CTE
where Len(Replace(Path, '>' + convert(varchar(max),Seed) + '>', ''))
  = LEN(Path) - Len('>' + convert(varchar(max),Seed) + '>') * 2