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