SQLTeam.com | Weblogs | Forums

Nested calls causing the error


#1

I am encountering an error below which is because of the nested operations in my code

Server: Msg 217, Level 16, State 1, Line 1
Maximum stored procedure, function, trigger, or view
nesting level exceeded (limit 32).

Please guide me how to rewrite the code so hat nested call can be avoided.
Can anyone help me how to do this?
Thanks in advance

The stored proc is as below:

ALTER PROCEDURE [dbo].[eusp_e5_eSM_SE_GetHistoryForItem]

@periodStart DATETIME,
@periodEnd DATETIME,
@itemSqlId SMALLINT,
@itemIncId INT,
@itemMetaTableSqlId SMALLINT,
@itemMetaTableIncId INT

AS

SET NOCOUNT ON

-- Declare temp tables
CREATE TABLE #StoringEvents
(
storingEventSqlId SMALLINT,
storingEventIncId INT,
containerSqlId SMALLINT,
containerIncId INT,
storingEventModifiedOn DATETIME,
storingEventModifiedBySqlId SMALLINT,
storingEventModifiedByIncId SMALLINT,
)

-- Declare variables
DECLARE @storingEventsCursor AS CURSOR
DECLARE @parentsCursor AS CURSOR
DECLARE @containerSqlId AS SMALLINT
DECLARE @containerIncId AS INT
DECLARE @storingEventModifiedOn AS DATETIME
DECLARE @containerPeriodStart DATETIME
DECLARE @containerPeriodEnd DATETIME
DECLARE @parentContainerSqlId SMALLINT
DECLARE @parentContainerIncId INT
DECLARE @debug INT
DECLARE @nextStoringEventCursor CURSOR

SET @debug = 0

SET @storingEventsCursor = CURSOR FOR
SELECT containerSqlId,
containerIncId,
storingEventModifiedOn
FROM #StoringEvents

-- Code
IF @debug = 1
SELECT 'START eusp_e5_eSM_SE_GetHistoryForItem', @periodStart AS periodStart, @periodEnd AS periodEnd, @itemSqlId AS itemSqlId, @itemIncId AS itemIncId, @itemMetaTableSqlId AS itemMetaTableSqlId, @itemMetaTableIncId AS itemMetaTableIncId

INSERT INTO #StoringEvents
SELECT storingEventSqlId,
storingEventIncId,
containerSqlId,
containerIncId,
storingEventModifiedOn,
storingEventModifiedBySqlId,
storingEventModifiedByIncId
FROM StoringsEvents
WHERE itemSqlId = @itemSqlId
AND itemIncId = @itemIncId
AND itemMetaTableSqlId = @itemMetaTableSqlId
AND itemMetaTableIncId = @itemMetaTableIncId
AND typeOfEventSqlId = 117
AND typeOfEventIncId = 1
AND ( @periodStart IS NULL
OR (storingEventModifiedOn >= @periodStart) )
AND ( @periodEnd IS NULL
OR (storingEventModifiedOn <= @periodEnd) )
AND isDeleted = 0

IF @debug = 1
SELECT '#StoringEvents', * FROM #StoringEvents

INSERT INTO #ItemHistory
SELECT * FROM #StoringEvents SE
WHERE ( SELECT COUNT(*)
FROM #ItemHistory IH
WHERE SE.storingEventSqlId = IH.storingEventSqlId
AND SE.storingEventIncId = IH.storingEventIncId) = 0 -- Insert only once

OPEN @storingEventsCursor

FETCH NEXT FROM @storingEventsCursor INTO @containerSqlId, @containerIncId, @storingEventModifiedOn
WHILE(@@FETCH_STATUS = 0)
BEGIN
SET @containerPeriodStart = @storingEventModifiedOn

SET @nextStoringEventCursor = CURSOR FOR
SELECT TOP(1) storingEventModifiedOn
FROM StoringsEvents
WHERE		storingEventModifiedOn > @containerPeriodStart
		AND itemSqlId = @itemSqlId
		AND itemIncId = @itemIncId
		AND itemMetaTableSqlId = @itemMetaTableSqlId
		AND itemMetaTableIncId = @itemMetaTableIncId
		AND isDeleted = 0
ORDER BY storingEventModifiedOn ASC

OPEN @nextStoringEventCursor
FETCH @nextStoringEventCursor INTO @containerPeriodEnd
CLOSE @nextStoringEventCursor
IF(@@FETCH_STATUS <> 0)
	SET @containerPeriodEnd = NULL

IF((@periodStart IS NOT NULL) AND (@containerPeriodStart < @periodStart))
	SET @containerPeriodStart = @periodStart
	
IF((@periodEnd IS NOT NULL) AND (@containerPeriodEnd > @periodEnd))
	SET @containerPeriodEnd = @periodEnd

EXEC [dbo].eusp_e5_eSM_SE_GetHistoryForItem @containerPeriodStart, @containerPeriodEnd, @containerSqlId, @containerIncId, 2, 413

SET @parentsCursor = CURSOR FOR
SELECT containerSqlId, containerIncId
FROM [dbo].eufn_e5_eSM_SE_GetParentContainersForItem(@storingEventModifiedOn, @containerSqlId, @containerIncId, 2, 413)

OPEN @parentsCursor
FETCH NEXT FROM @parentsCursor INTO @parentContainerSqlId, @parentContainerIncId
WHILE(@@FETCH_STATUS = 0)
BEGIN
	EXEC [dbo].eusp_e5_eSM_SE_GetHistoryForItem @containerPeriodStart, @containerPeriodEnd, @parentContainerSqlId, @parentContainerIncId, 2, 413
	FETCH NEXT FROM @parentsCursor INTO @parentContainerSqlId, @parentContainerIncId
END
CLOSE @parentsCursor

FETCH NEXT FROM @storingEventsCursor INTO @containerSqlId, @containerIncId, @storingEventModifiedOn

END
CLOSE @storingEventsCursor
DROP TABLE #StoringEvents


#2

Looks like you have a recursive call to this proc:

Is that what you intended? If so, what stops the recursion?


#3

Yes, gbritton, this stored proc is giving recursive calls.
How can I remove those recursive calls so that the error message can be avoided?


#4

Not so easy to answer. It depends totally on the logic you need. FWIW Recursion can be OK as long as there is something to stop it recursing. A quick read of the proc does not give enough info about what the best approach is, and I don't have the benefit of knowing your business rule or what you are trying to achieve


#5

You can use @@nestlevel to see the nesting levels. Insert a statement like

SELECT @@NESTLEVEL

somewhere in your stored proc.

But that may only be of limited use to you. There seems to be something wrong/unstable about the way the recursive calls are made. You are making those calls within a cursor which is in another cursor.


#6

Hi JamesK, thank you for that insight.
I have add the statement SELECT @@NESTLEVEL in my query at different points.
But everywhere it is returning a value 0, even at the previous line where it is breaking.
Why is it so? What else can I do?


#7

Without being able to run your code (which I cannot do because I don't have the data and the tables), it is hard to say.

Look at the example Microsoft has on the page. Run that example to make sure you are getting the output as they describe. Then work your way up from there, comparing it to your code where you make nested calls.


#8

I have used like PRINT @@NESTLEVEL.
This is now printing the nesting level.
I am now converting the cursors used in the stored proc to temp table code.
Please guide me through some inputs how I can achieve this.