Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

[indian143]

Hi All, I am getting the following error when I am running the stored procedure.

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

When I remove the following part, it is not throwing that error. Can you please let me know where am I missing, am I making something wrong, is it related to Line 26 as in the message.

	WITH CTE AS
		(
		SELECT FILTER.GroupKey,
			FILTER.ClassScheduleId,
			SUM(SlotCount) AS SlotCount
		FROM	#SlotInformation INFO
			INNER JOIN #Base FILTER
				ON FILTER.ClassScheduleId  = INFO.ClassScheduleId
				AND (
						@Level = 'Class'
					OR	FILTER.UseInAggs = 1
					)
		WHERE	SlotPlanTypeId IN (@SLOTPLAN\_ECEAP, @SLOTPLAN\_ECEAPSPED)
		GROUP BY
			FILTER.GroupKey,
			FILTER.ClassScheduleId
		)
		
	INSERT	INTO #Results
		(GroupKey, Identifier, Name, Value)
	SELECT	WEEK.GroupKey,
		'NIEER' AS Identifier,
		'Number of ECEAP slots in ECEAP classes meeting 8 or more hours per day' AS Name,
		SUM(CASE WHEN WEEK.AverageHoursPerDay >= 8 THEN CTE.SlotCount ELSE 0 END) AS Value
	FROM	#WeeklyOverview WEEK
		INNER JOIN CTE
			ON CTE.GroupKey = WEEK.GroupKey
			AND CTE.ClassScheduleId = WEEK.ClassScheduleId
	GROUP BY
		WEEK.GroupKey
		
	UNION ALL
	
	SELECT	WEEK.GroupKey,
		'NIEER' AS Identifier,
		'Number of ECEAP slots in ECEAP classes meeting at least 4 hours but fewer than 8 hours per day' AS Name,
		SUM(CASE WHEN WEEK.AverageHoursPerDay >= 4 AND WEEK.AverageHoursPerDay < 8 THEN CTE.SlotCount ELSE 0 END) AS Value
	FROM	#WeeklyOverview WEEK
		INNER JOIN CTE
			ON CTE.GroupKey = WEEK.GroupKey
			AND CTE.ClassScheduleId = WEEK.ClassScheduleId
	GROUP BY
		WEEK.GroupKey
		
	UNION ALL
	
	SELECT	WEEK.GroupKey,
		'NIEER' AS Identifier,
		'Number of ECEAP slots in ECEAP classes meeting fewer than 4 hours per day' AS Name,
		SUM(CASE WHEN WEEK.AverageHoursPerDay < 4 THEN CTE.SlotCount ELSE 0 END) AS Value
	FROM	#WeeklyOverview WEEK
		INNER JOIN CTE
			ON CTE.GroupKey = WEEK.GroupKey
			AND CTE.ClassScheduleId = WEEK.ClassScheduleId
	GROUP BY
		WEEK.GroupKey;

Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

[Richard Deeming]

A common table expression can only be used in the statement which immediately follows its declaration. In your code, that's the SELECT * FROM CTE; statement. If you want to re-use it in the INSERT INTO statement, then you have to re-state it:

WITH CTE As
(
...
)
SELECT * FROM CTE;

WITH CTE As
(
...
)
INSERT INTO #Results
...

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[indian143]

I am sorry but that was not the issue even without putting the select statement error was coming. I put that select statement in process of debugging it.

Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."