超出最大存储过程嵌套级别（限制32） [英] Maximum stored procedure nesting level exceeded (limit 32)

问题描述

我尝试使用sp和cursor删除多个重复项，但是它会抛出错误



我尝试过：

I try to delete multiple duplicates using sp and cursor, but it throw error

What I have tried:

creat proc uspfixdup
(
  @paramID int
)
as


declare @id int
declare _cursor CURSOR LOCAL FAST_FORWARD FOR
select  id
from departments dp
where dp.roleid=1
group by id
having count(*)>1
order by id desc

open _cursor

fetch next FROM _cursor INTO @id

while @@FETCH_STATUS = 0
BEGIN
print @id

 exec uspfixdup @paramID = @id


fetch next FROM _cursor INTO @id

END

close _cursor
DEALLOCATE _cursor


DECLARE @deptID1 int
DECLARE @deptID2 int

SELECT @deptID1 = deptID
FROM   departments 
WHERE  ID = @paramID
AND    RoleID = 1


SELECT deptID2 = deptID
FROM   departments 
WHERE  ID = @paramID
AND    RoleID = 1
AND    deptID <> @deptID1


IF @deptid1 >1 AND @deptID2>1
BEGIN

  IF @depti1 < @deptID2
    DELETE FROM departments  WHERE deptID = @detID1
  ELSE
    DELETE FROM departments  WHERE deptID = @deptID2
END
ELSE
  PRINT 'no duplicate '
GO

推荐答案

正如评论中所提到的，错误意味着您一遍又一遍地递归调用存储过程。您应该评估是否需要游标，如果实际需要递归函数则更是如此。一般来说两者都是糟糕的想法。



很高兴看到你工作了。

As mentioned in the comments, the error means you are calling your stored procedure recursively over and over too many times. You should evaluate if a cursor is necessary and even more so if a recursive function is actually necessary. Both are bad ideas generally speaking.

Glad to see you got it working though.

这篇关于超出最大存储过程嵌套级别（限制32）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！