Unnamed

DECLARE @SQLPrefix				nvarchar(max)
		, @SQLPostfix			nvarchar(max)
		, @SQL					nvarchar(max)

DECLARE @BatchID			bigint

SET @BatchID = 2335

DECLARE @MaxNumberRecursiveSections			tinyint
		, @Counter				int
		, @vCounter				varchar(5)
		, @Spacer				varchar(5)

;with cte as (
	SELECT s.ID [ParentID], s.ID, 1 [Level]
	FROM Section s
	WHERE ID = spRef
		UNION ALL
	SELECT c.ParentID, s.ID, [Level] + 1
	FROM cte c
		INNER JOIN Section s ON s.spRef = c.ID
	WHERE s.spRef <> s.ID
)

SELECT @MaxNumberRecursiveSections = MAX([Level])
FROM cte

IF (OBJECT_ID('testProcedure', 'P') IS NOT NULL)
	DROP PROCEDURE testProcedure

SELECT @SQLPrefix = '
	CREATE PROCEDURE dbo.testProcedure
	(
		@BatchID					bigint
	)
	AS
		BEGIN
			SELECT
				Ba.ID "@BatchID",
					(SELECT
						Student.ID "@ID",
						Student.studentID "@studentID",
						Student.nameLast "@nameLast",
						Student.nameFirst "@nameFirst",
						Student.EssayScore "@EssayScore"'

SELECT @SQLPostfix = '
						FROM
							Student
						WHERE
							Student.BatchID = Ba.ID
						ORDER BY
							Student.nameLast, Student.[ID]
						FOR XML PATH(''St''), TYPE)
			FROM
				Batch Ba
			WHERE
				Ba.ID = @BatchID
			ORDER BY
				Ba.ID
			FOR XML PATH(''SectionSummary'')
		END'


SET @Counter = 1
SET @SQL = ''

WHILE (@Counter <= @MaxNumberRecursiveSections)
BEGIN
	SELECT @vCounter = convert(varchar, @Counter)

	SELECT @Spacer = REPLICATE('	', @Counter)

	SELECT @SQL = @SQL + ',
' + @Spacer + '						(SELECT
' + @Spacer + '							Sec' + @vCounter + '.ID "@SecID",
' + @Spacer + '							Sec' + @vCounter + '.SectionName "@sectionName",
' + @Spacer + '							Ans.Correct "@CORRECT",
' + @Spacer + '							Ans.Wrong "@WRONG",
' + @Spacer + '							Ans.Omit "@OMIT",
' + @Spacer + '							Ans.Error "@ERROR",
' + @Spacer + '							ISNULL(cast(SS.scaledScore as varchar),''-'') "@ScaledScore",
' + @Spacer + '							ISNULL(Perc.percentile,-1) "@Percentile",
' + @Spacer + '							ISNULL(Perc.percentileRange, NULL) "@PercentileRange",
' + @Spacer + '							GE.GreEq + '''' "@GreEq",
' + @Spacer + '							GE.Grade + 0 "@GradeEquiv",
' + @Spacer + '							Perc.Stanine + 0 "@Stanine"'

	SET @Counter = @Counter + 1
END

SELECT @Counter = @Counter - 1

WHILE (@Counter > 0)
BEGIN
	SELECT @vCounter = convert(varchar, @Counter)

	SELECT @Spacer = REPLICATE('	', @Counter)

	SELECT @SQL = @SQL + '
' + @Spacer + '							FROM
' + @Spacer + '								Section Sec' + @vCounter + '
' + @Spacer + '								LEFT JOIN TestView2 Ans ON Sec' + @vCounter + '.ID = Ans.SecID and Ans.stID = Student.ID
' + @Spacer + '								INNER JOIN [Level] Lev ON Sec' + @vCounter + '.pRef = Lev.ID and Lev.ID=Ba.levelID
' + @Spacer + '								LEFT JOIN ScaledScore SS ON Sec' + @vCounter + '.ID = SS.pRef and cast(round(Ans.CORRECT + Ans.BIAS,0) as int) BETWEEN SS.rawScore and ISNULL(SS.rawScoreHighRange,SS.rawScore)
' + @Spacer + '									AND ((Student.EssayScore IS NOT NULL and (cast(SS.EssayScore as decimal(16,2)) = cast(Student.EssayScore as decimal(16,2)) or round(cast(SS.EssayScore as decimal(16,2)),0) = round(cast(Student.EssayScore as decimal(16,2)),0))) or (Student.EssayScore IS NOT NULL and SS.EssayScore IS NULL) or (Student.EssayScore IS NULL))
' + @Spacer + '								LEFT JOIN Percentile Perc ON SS.scaledScore BETWEEN Perc.scaledScoreLow AND ISNULL(scaledScoreHigh, scaledScoreLow) AND Sec' + @vCounter + '.ID = Perc.pRef AND ((CASE WHEN ISNUMERIC(Student.Grade)=1 THEN Student.Grade ELSE NULL END) = Perc.Grade or Perc.Grade = -1)
' + @Spacer + '								LEFT JOIN GradeEquiv GE ON SS.scaledScore BETWEEN GE.scaledScoreLow and GE.scaledScoreHigh and Sec' + @vCounter + '.ID = GE.pRef
' + @Spacer + '								  AND ((Ba.Grade IS NULL and (CASE WHEN ISNUMERIC(Student.Grade)=1 THEN Student.Grade ELSE NULL END) = Perc.Grade) or (Ba.Grade IS NOT NULL and Ba.Grade = Perc.Grade) or (Perc.Grade = -1 and Student.Grade IS NULL and Ba.Grade IS NULL))
' + @Spacer + '							WHERE
' + @Spacer + '								'
	IF (@Counter = 1)
	BEGIN
		SELECT @SQL = @SQL + 'Sec' + @vCounter + '.spRef = Sec' + @vCounter + '.ID'
	END
	ELSE
	BEGIN
		SELECT @SQL = @SQL + 'Sec' + @vCounter + '.spRef = Sec' + convert(varchar, @Counter - 1) + '.ID and NOT (Sec' + @vCounter + '.spRef = Sec' + @vCounter + '.ID)'
	END

	SELECT @SQL = @SQL + '
' + @Spacer + '							ORDER BY Sec' + @vCounter + '.OrderWeight
' + @Spacer + '								FOR XML PATH(''Sec''),TYPE)'

	SET @Counter = @Counter - 1
END

SELECT @SQL = @SQLPrefix + @SQL + @SQLPostfix
--SELECT @SQL
EXEC sp_ExecuteSQL @SQL
--		, N'@BatchID				bigint'
--		, @BatchID = @BatchID