sql - 在 Visual Studio (ado.net) 中超出了最大存储过程、函数、触发器或 View 嵌套级别，但在 SQL Server 中没有

Question

我有一个递归 SQL 函数的问题。最初的问题是我有一份员工名单，他们每个人都接受过各种培训。这些培训中的每一个都有一些先决条件。例如，要获得 1 类驾驶执照，您必须拥有 5 类。如果我删除 5 类，我需要检查禁用 1 类。

现在因为这是一棵没有固定最大深度的树(实际上我在 10ish 时停止检查)，所以我决定使用递归。我写了两个存储过程

[dbo].[spCheckTrainingPreqs]
@PIN int,
@training_id int,
@missingTraining int OUTPUT

和

[dbo].[spCheckTrainingPreqsEmployee] 
@PIN int

现在，当我使用员工编号调用 spCheckTrainingPreqsEmployee 时，它​​会为每个 pin/training_id 组合创建一个光标并调用 spCheckTrainingPreqs。 spCheckTrainingPreqs 使用递归本地游标来遍历树。

现在是踢球者。它在 Visual Studio 中运行良好

DECLARE @return_value int

EXEC    @return_value = [dbo].[spCheckTrainingPreqsEmployee]
        @PIN = 12673

SELECT  'Return Value' = @return_value

GO

但是，如果我进入 Visual Studio 并将其作为存储过程添加到表适配器而没有返回，我会收到错误“超出最大存储过程、函数、触发器或 View 嵌套级别(限制 32)”

我已经检查过我的表都没有触发器。唯一的复杂性是，许多表都有基于它们的 View ，带有子查询，甚至是 c# 编码的自定义连接函数。请记住，从 SQL 管理器运行它可以正常工作。

完整代码供引用

/****** Object:  StoredProcedure [dbo].[spCheckTrainingPreqs]    Script Date: 08/31/2010 10:13:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[spCheckTrainingPreqs]
@PIN int,
@training_id int,
@missingTraining int OUTPUT
AS
-- get the Prerequisites
declare @requiresID int
declare CurPrereqs cursor local for SELECT RequiresID FROM TrainingPrerequisites WHERE SourceID = @training_id
SET @missingTraining = 0
OPEN CurPrereqs
FETCH NEXT FROM CurPrereqs INTO @requiresID 
WHILE @@FETCH_STATUS = 0
BEGIN
    IF (@missingTraining = 0) -- stop when a missing training is found
    BEGIN
        IF (SELECT count(training_id) FROM employee_training WHERE PIN = @PIN AND training_id = @requiresID GROUP BY training_id) = 1
        BEGIN
            --they have the training 
            IF (@@NESTLEVEL  < 10) -- we only check 10 levels deep
            BEGIN
                EXEC spCheckTrainingPreqs @PIN, @requiresID, @missingTraining
                UPDATE employee_training SET missingPreReq = @missingTraining WHERE training_id = @training_id and PIN = @PIN;
            END
        END
        ELSE
        BEGIN
            SET @missingTraining = @requiresID
            UPDATE employee_training SET missingPreReq = @missingTraining WHERE training_id = @training_id and PIN = @PIN;
            CLOSE CurPrereqs
            DEALLOCATE CurPrereqs;
            RETURN 
        END

    END
FETCH NEXT FROM CurPrereqs INTO @requiresID 
END
CLOSE CurPrereqs
DEALLOCATE CurPrereqs;

和

/****** Object:  StoredProcedure [dbo].[spCheckTrainingPreqsEmployee]    Script Date: 08/31/2010 10:28:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 08/31/2010
-- Description: Checks all pre reqs for an employee
-- =============================================
ALTER PROCEDURE [dbo].[spCheckTrainingPreqsEmployee] 
    @PIN int
AS
BEGIN
    SET NOCOUNT ON;
    declare @training_id int
    declare @missingTraining int
    SET @missingTraining = 0
    declare CurPrereqsE cursor local for SELECT training_id FROM employee_training WHERE PIN = @PIN
    OPEN CurPrereqsE
    FETCH NEXT FROM CurPrereqsE INTO @training_id 
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC spCheckTrainingPreqs @PIN, @training_id, @missingTraining
    FETCH NEXT FROM CurPrereqsE INTO @training_id 
    END
    CLOSE CurPrereqsE
    DEALLOCATE CurPrereqsE;
END

Answer 1

当你说:

Remember running it from SQL manager works fine

您使用什么值来测试参数？ VS中的错误很可能是设计者的调用引起的。值得检查您是否使用与设计者使用的参数相同的参数调用该过程。

尝试使用 SQL Profiler 跟踪 SQL 调用并准确检查 VS 传递的值。然后，您可以从 SQL 管理器中执行完全相同的查询。我的猜测是你会得到完全相同的结果。