SQL 如何处理“超出最大存储过程、函数、触发器或视图嵌套级别（限制 32）”。
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16481130/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to deal with "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)."
提问by asbd
i was asked to Create script that will expect whoever runs it to provide an employee id. Locate all employees that the provided employee supervises in any depth.
我被要求创建脚本，希望运行它的人提供员工 ID。找到提供的员工在任何深度监督的所有员工。
My code is :
我的代码是：
CREATE FUNCTION [dbo].[GetNames] (@V uniqueidentifier)
RETURNS @OldNames TABLE (EMP_NAME varchar(50))
AS
BEGIN
DECLARE @master uniqueidentifier
SET @master=(SELECT EMP_Supervisor FROM Employee WHERE EMP_ID=@v)
IF @master=NULL return
INSERT INTO @OldNames(EMP_NAME)
SELECT (SELECT EMP_NAME FROM Employee WHERE EMP_ID = @master)
FROM Employee
UNION
SELECT EMP_NAME FROM GetNames(@master)
RETURN
END
And when i want to see if it works, i execute this :
当我想看看它是否有效时，我执行以下操作：
SELECT * from GetNames('561e2d88-a747-460f-99e1-cfb1d3d8ca5c')
where "561e2d88-a747-460f-99e1-cfb1d3d8ca5c" is an ui of an employee and i get this as an exception:
其中“ 561e2d88-a747-460f-99e1-cfb1d3d8ca5c”是员工的用户界面，我将此作为例外：
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
超出最大存储过程、函数、触发器或视图嵌套级别（限制为 32）。
can you please help me? Thanks in advance!!
你能帮我么？提前致谢！！
回答by Adriaan Stander
Lets say you are using SQL Server
假设您正在使用 SQL Server
Have a look at the following example
看看下面的例子
DECLARE @EmployeeStructure TABLE(
ID INT,
Name VARCHAR(MAX),
ManagerID INT
)
INSERT INTO @EmployeeStructure SELECT 1, 'a', NULL
INSERT INTO @EmployeeStructure SELECT 2, 'b', 1
INSERT INTO @EmployeeStructure SELECT 3, 'c', 1
INSERT INTO @EmployeeStructure SELECT 4, 'd', 2
INSERT INTO @EmployeeStructure SELECT 5, 'e', 2
INSERT INTO @EmployeeStructure SELECT 6, 'f', 2
DECLARE @EmployeeID INT = 2
;WITH Employee AS (
SELECT Name, ID
FROM @EmployeeStructure e
WHERE ManagerID = @EmployeeID
UNION ALL
SELECT es.Name,
es.ID
FROM Employee e INNER JOIN
@EmployeeStructure es ON e.ID = es.ManagerID
)
SELECT Name
FROM Employee
OPTION (MAXRECURSION 0)
SQL Fiddle Demo
SQL 小提琴演示
Further to that maybe have a look at
除此之外，也许看看
Using Common Table Expressionsand Recursive Queries Using Common Table Expressions
Also,
还，
MAXRECURSION number
Specifies the maximum number of recursions allowed for this query. number is a nonnegative integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is not specified, the default limit for the server is 100.
When the specified or default number for MAXRECURSION limit is reached during query execution, the query is ended and an error is returned.
Because of this error, all effects of the statement are rolled back. If the statement is a SELECT statement, partial results or no results may be returned. Any partial results returned may not include all rows on recursion levels beyond the specified maximum recursion level.
最大递归数
指定此查询允许的最大递归数。number 是 0 到 32767 之间的非负整数。指定 0 时，不应用任何限制。如果未指定此选项，则服务器的默认限制为 100。
当查询执行期间达到 MAXRECURSION 限制的指定或默认数量时，查询将结束并返回错误。
由于此错误，该语句的所有效果都将回滚。如果语句是 SELECT 语句，则可能会返回部分结果或不返回结果。返回的任何部分结果可能不包括超出指定最大递归级别的递归级别上的所有行。
