Maximum stored procedure, function, trigger, or nesting level of a view is exceeded in Visual Studio (ado.net), but not on the SQL server

Question

I have a problem with a recursive SQL function. The initial problem is that I have a list of employees, each of whom has different trainings. Each of these trainings has some preliminary requirements. For example, to have a license for class 1 drivers, you must have class 5. If I remove class 5, I need to check if class 1 is disabled.

Now that this tree is without a fixed maximum depth (I actually stop checking for 10ish), I decided to use recursion. I wrote two stored procedures

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

and

[dbo].[spCheckTrainingPreqsEmployee] 
@PIN int

, spCheckTrainingPreqsEmployee , pin/training_id spCheckTrainingPreqs. spCheckTrainingPreqs .

. .

DECLARE @return_value int

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

SELECT  'Return Value' = @return_value

GO

, " , , ( 32)"

, . , , , #. , 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

:

, SQL Manager

? VS, , . , , .

SQL Profiler SQL , VS . SQL. , .