UFN_CLASSMEETINGGROUP_GETFACULTYSTRING

create function dbo.UFN_CLASSMEETINGGROUP_GETFACULTYSTRING
(
    @CLASSMEETINGGROUPID uniqueidentifier
)
returns nvarchar(1000)
with execute as caller
as begin

    declare @FACULTYLIST nvarchar(1000)
    select
        @FACULTYLIST = coalesce(@FACULTYLIST + '; ', '') + CONSTITUENT.NAME
    from dbo.CLASSMEETINGGROUP
        inner join dbo.FACULTYCLASSMEETINGGROUP on CLASSMEETINGGROUP.ID = FACULTYCLASSMEETINGGROUP.CLASSMEETINGGROUPID
        inner join dbo.FACULTYCOURSE on FACULTYCLASSMEETINGGROUP.FACULTYCOURSEID = FACULTYCOURSE.ID
        inner join dbo.CONSTITUENT on FACULTYCOURSE.FACULTYID = CONSTITUENT.ID
    where CLASSMEETINGGROUP.ID = @CLASSMEETINGGROUPID
    group by CONSTITUENT.ID, CONSTITUENT.NAME
    order by CONSTITUENT.NAME asc

    return @FACULTYLIST
end