UFN_STUDENT_GETCLASSMEETINGGROUPS

CREATE function dbo.UFN_STUDENT_GETCLASSMEETINGGROUPS
(
    @STUDENTID uniqueidentifier,
    @ACADEMICYEARNAMECODEID uniqueidentifier = null,
    @SESSIONNAMECODEID uniqueidentifier = null,
    @TERMNAMECODEID uniqueidentifier = null,
    @SCHOOLID uniqueidentifier = null,
    @STATUSCODE tinyint = null
)
returns @RESULTS TABLE
(
    ID uniqueidentifier,
    CLASSID uniqueidentifier,
    NAME nvarchar(200),
    CLASSNAME nvarchar(60),
    TERM nvarchar(1000),
    FACULTY nvarchar(1000),
    BLOCK nvarchar(100),
    MEETINGS nvarchar(1000),
    STATUS nvarchar(50),
    STATUSDATE date
)
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,
        PATTERNBLOCK.NAME as BLOCK,
        dbo.UFN_CLASSMEETINGTEMPLATE_GETTIMELIST_FROMCLASSMEETINGGROUP(CLASSMEETINGGROUP.[ID]) as MEETINGS,
        case when STUDENTCLASSMEETINGGROUP.STATUSCODE <> 0 then STUDENTCLASSMEETINGGROUP.STATUS else '' end as STATUS,
        STUDENTCLASSMEETINGGROUP.STATUSDATE
    from dbo.CLASSMEETINGGROUP
        inner join dbo.STUDENTCLASSMEETINGGROUP on CLASSMEETINGGROUP.ID = STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID
        inner join dbo.STUDENTCOURSE on STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID = STUDENTCOURSE.ID
        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.STUDENTCOURSE.STUDENTID = @STUDENTID
        and (ACADEMICYEAR.ACADEMICYEARNAMECODEID = @ACADEMICYEARNAMECODEID or @ACADEMICYEARNAMECODEID is null)
        and (SESSION.SESSIONNAMECODEID = @SESSIONNAMECODEID or @SESSIONNAMECODEID is null)
        and (TERM.TERMNAMECODEID = @TERMNAMECODEID or @TERMNAMECODEID is null)
    and (STUDENTCLASSMEETINGGROUP.STATUSCODE = @STATUSCODE or @STATUSCODE is null)
    order by COURSE.[COURSEID], CLASS.[SECTION], TERM.STARTDATE

    return
end