USP_DATALIST_COURSERESTRICTION

CREATE procedure dbo.USP_DATALIST_COURSERESTRICTION(@COURSEID uniqueidentifier)
as
    set nocount on;

select 
    COURSERESTRICTION.ID,
    ACADEMICYEARNAMECODE.DESCRIPTION as [ACADEMICYEAR],
    SESSIONNAMECODE.DESCRIPTION as [SESSION],
    LENGTHINTERMS,

    (select stuff((
        select '; ' + TERMNAMECODE.DESCRIPTION
        from dbo.COURSERESTRICTIONSTARTTERM 
        join dbo.TERM on TERM.ID = dbo.UFN_TERM_GET_FORSCHOOL_BYDATE(COURSE.SCHOOLID, COURSERESTRICTIONSTARTTERM.STARTDATE, COURSERESTRICTIONSTARTTERM.ENDDATE )
        join dbo.TERMNAMECODE on TERM.TERMNAMECODEID = TERMNAMECODE.ID
        where COURSERESTRICTIONSTARTTERM.COURSERESTRICTIONID = COURSERESTRICTION.ID
        order by TERM.STARTDATE
        for xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, ''))
    as [STARTTERMS],

    case when PATTERN.ID is null
        then 'Meeting' 
        else 'Pattern' 
    end as [RESTRICTEDBY],

    PATTERN.NAME as [PATTERN]
from 
    dbo.V_COURSERESTRICTION as COURSERESTRICTION
    join
        dbo.COURSE on COURSE.ID = COURSERESTRICTION.COURSEID
    join
        dbo.SESSION on SESSION.ID = dbo.UFN_SESSION_GET_FORSCHOOL_BYDATE(COURSE.SCHOOLID, COURSERESTRICTION.STARTDATE, COURSERESTRICTION.ENDDATE)
    join 
        dbo.SESSIONNAMECODE on SESSIONNAMECODE.ID = SESSION.SESSIONNAMECODEID
    join 
        dbo.ACADEMICYEAR on ACADEMICYEAR.ID = SESSION.ACADEMICYEARID
    join 
        dbo.ACADEMICYEARNAMECODE on ACADEMICYEAR.ACADEMICYEARNAMECODEID = ACADEMICYEARNAMECODE.ID
    left join
        dbo.PATTERN on PATTERN.ID = COURSERESTRICTION.PATTERNID    
where 
    COURSE.ID = @COURSEID
order by
    COURSERESTRICTION.STARTDATE