USP_DATALIST_ACADEMICYEAR

                CREATE procedure dbo.USP_DATALIST_ACADEMICYEAR
                    (
                        @SCHOOLID uniqueidentifier = null
                    )
                as
                    set nocount on;

                    select ACADEMICYEAR.ID,
                           ACADEMICYEARNAMECODE.DESCRIPTION AS NAME,
                           ACADEMICYEAR.STARTDATE,
                           ACADEMICYEAR.ENDDATE,
                           (select stuff((select '; ' + dbo.SESSIONNAMECODE.DESCRIPTION
                               from dbo.SESSION
                               join dbo.TERM on SESSION.ID = TERM.SESSIONID
                               inner join dbo.SESSIONNAMECODE on dbo.SESSION.SESSIONNAMECODEID = dbo.SESSIONNAMECODE.ID
                               where dbo.SESSION.ACADEMICYEARID = dbo.ACADEMICYEAR.ID
                               group by SESSION.ID, SESSIONNAMECODE.DESCRIPTION
                               order by min(TERM.STARTDATE)
                               for xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '')) AS SESSIONS,
                           CONSTITUENT.KEYNAME as SCHOOLNAME
                    from dbo.ACADEMICYEAR
                        inner join CONSTITUENT
                            on ACADEMICYEAR.SCHOOLID = CONSTITUENT.ID
                        inner join dbo.ACADEMICYEARNAMECODE 
                            on ACADEMICYEAR.ACADEMICYEARNAMECODEID = ACADEMICYEARNAMECODE.ID
                    where (@SCHOOLID is null) or (ACADEMICYEAR.SCHOOLID = @SCHOOLID)
                    order by ACADEMICYEAR.STARTDATE, ACADEMICYEAR.SCHOOLID