UFN_COURSE_GETCLASSMEETINGGROUPS

create function dbo.UFN_COURSE_GETCLASSMEETINGGROUPS
(
    @COURSEID uniqueidentifier,
    @ACADEMICYEARNAMECODEID uniqueidentifier = null,
    @SESSIONNAMECODEID uniqueidentifier = null,
    @TERMNAMECODEID uniqueidentifier = null,
    @SCHOOLID uniqueidentifier = null
)
returns @RESULTS TABLE
(
    ID uniqueidentifier,
    CLASSID uniqueidentifier,
    NAME nvarchar(200),
    CLASSNAME nvarchar(60),
    TERM nvarchar(1000),
    FACULTY nvarchar(1000),
    NUMBERENROLLED int,
    BLOCK nvarchar(100),
    MEETINGS nvarchar(1000)
)
as begin
    insert into @RESULTS
    select
        CLASSMEETINGGROUP.ID,
        CLASSMEETINGGROUP.CLASSID,
        COURSE.[COURSEID] + ' - ' + CLASS.[SECTION] as NAME,
        CLASS.[NAME] as CLASSNAME,
        dbo.UFN_TERMNAMECODE_GETDESCRIPTION(TERM.TERMNAMECODEID) as TERM,
        dbo.UFN_CLASSMEETINGGROUP_GETFACULTYSTRING(CLASSMEETINGGROUP.[ID]) as FACULTY,
        dbo.UFN_CLASSMEETINGGROUP_GETSTUDENTCOUNT(CLASSMEETINGGROUP.[ID]) as NUMBERENROLLED,
        PATTERNBLOCK.NAME as BLOCK,
        dbo.UFN_CLASSMEETINGTEMPLATE_GETTIMELIST_FROMCLASSMEETINGGROUP(CLASSMEETINGGROUP.[ID]) as MEETINGS
    from dbo.CLASSMEETINGGROUP
        inner join dbo.CLASS on CLASSMEETINGGROUP.CLASSID = CLASS.ID
        inner join dbo.COURSE on dbo.CLASS.COURSEID = dbo.COURSE.ID
        inner join dbo.ACADEMICYEAR on COURSE.SCHOOLID = ACADEMICYEAR.SCHOOLID
        inner join dbo.SESSION on ACADEMICYEAR.ID = SESSION.ACADEMICYEARID
        inner join dbo.TERM on SESSION.ID = TERM.SESSIONID
            and TERM.STARTDATE = CLASSMEETINGGROUP.STARTDATE
            and TERM.ENDDATE = CLASSMEETINGGROUP.ENDDATE
        left join dbo.PATTERNBLOCK on CLASSMEETINGGROUP.PATTERNBLOCKID = PATTERNBLOCK.ID
    where dbo.CLASS.COURSEID = @COURSEID
        and (ACADEMICYEAR.ACADEMICYEARNAMECODEID = @ACADEMICYEARNAMECODEID or @ACADEMICYEARNAMECODEID is null)
        and (SESSION.SESSIONNAMECODEID = @SESSIONNAMECODEID or @SESSIONNAMECODEID is null)
        and (TERM.TERMNAMECODEID = @TERMNAMECODEID or @TERMNAMECODEID is null)
    order by COURSE.[COURSEID], CLASS.[SECTION], TERM.STARTDATE

    return
end