编写自动生成nordre值的插入存储过程触发嵌套层级超限报错

首先，这个错误的核心原因是你的存储过程触发了递归调用，超过了SQL Server默认的32层嵌套限制。大概率是以下几种情况之一：

• 插入数据的目标表有触发器，而触发器又调用了pr_insert_lignes或者间接触发了它的执行；
• 你调用的pr_getprix存储过程内部，修改了和pr_insert_lignes相关的表，或者间接调用了pr_insert_lignes；
• 计算nordre的逻辑不小心引发了循环（比如用了自定义函数，而函数又触发了对目标表的操作）。

第一步：排查递归来源 #

先做这几个排查，定位问题根源：

• 检查目标表（比如你插入数据的lignes表）是否有触发器，查看触发器代码里是否调用了pr_insert_lignes，或者有没有修改会触发这个存储过程的其他表；
• 单独执行pr_getprix，确认它只是读取数据的纯查询过程，不会修改任何可能触发pr_insert_lignes的表；
• 简化pr_insert_lignes，先去掉pr_getprix的调用，只测试插入nordre的逻辑，如果不报错，说明问题出在pr_getprix的调用环节。

第二步：修正存储过程逻辑 #

下面是一个优化后的pr_insert_lignes示例（假设你是按某个父ID分组计算nordre，你可以根据实际业务调整分组条件），同时避免递归触发：

CREATE PROCEDURE pr_insert_lignes
    @id_parent INT, -- 分组依据字段，根据你的实际表结构调整
    @autre_champ VARCHAR(50), -- 其他需要插入的字段
    -- 更多参数根据实际需求添加
AS
BEGIN
    SET NOCOUNT ON; -- 避免返回影响行数的消息，减少不必要的交互

    -- 1. 安全获取当前最大nordre值，用ISNULL处理无记录的情况
    DECLARE @current_max_nordre INT;
    SELECT @current_max_nordre = ISNULL(MAX(nordre), 0)
    FROM lignes
    WHERE id_parent = @id_parent; -- 替换成你的实际分组条件

    -- 2. 调用pr_getprix获取prix值，确保这个过程是纯查询无修改
    DECLARE @prix DECIMAL(18,2);
    EXEC pr_getprix @param1 = '你的参数值', @prix_output = @prix OUTPUT; -- 替换成pr_getprix的实际参数

    -- 3. 执行插入，确保插入操作不会触发递归调用的触发器
    INSERT INTO lignes (nordre, id_parent, autre_champ, prix)
    VALUES (@current_max_nordre + 1, @id_parent, @autre_champ, @prix);
END

第三步：避免递归的额外注意事项 #

• 如果目标表有触发器，一定要在触发器里添加递归终止判断：

  CREATE TRIGGER trg_lignes_after_insert ON lignes
  AFTER INSERT
  AS
  BEGIN
      -- 当嵌套层级超过1时，终止触发器执行，避免递归
      IF TRIGGER_NESTLEVEL() > 1
          RETURN;
  
      -- 触发器的其他逻辑...
  END
  

• 确保pr_getprix不会修改任何会触发pr_insert_lignes的表，如果它必须修改数据，要调整逻辑避免循环调用；
• 计算nordre时，尽量用单次查询获取最大值，不要用游标或循环操作，减少触发额外逻辑的可能。

验证方法 #

1. 先单独运行pr_getprix，确认它能正常返回prix值，且不会触发任何错误；
2. 调用简化版的pr_insert_lignes（去掉pr_getprix），测试插入nordre的逻辑是否正常；
3. 逐步加入pr_getprix的调用，确认每一步都不会触发嵌套超限错误。

内容的提问来源于stack exchange，提问作者Beginnerdevelopper