USP_STUDENT_GETSCHEDULINGPROFILE

CREATE procedure dbo.USP_STUDENT_GETSCHEDULINGPROFILE
(
    @ID uniqueidentifier,
    @SESSION_STARTDATE date = null output,
    @SESSION_ENDDATE date = null output,
    @STUDENT_NAME nvarchar(100) = null output,
    @GRADELEVELID uniqueidentifier = null output,
    @GRADELEVEL nvarchar(200) = null output,
    @ADVISOR nvarchar(1000) = null output,
    @HOMEROOMTEACHER nvarchar(1000) = null output,
    @GENDER nvarchar(100) = null output,
    @SCHOOLGRADELEVELID uniqueidentifier = null output
)
as
begin
    select 
        @STUDENT_NAME = CONSTITUENT.NAME,
        @GENDER = CONSTITUENT.GENDER
    from CONSTITUENT
    where ID = @ID

    -- Build the SPE list, which will parse to build lists

    declare @SPE table
    (
        GRADELEVEL nvarchar(100),
        GRADELEVELID uniqueidentifier,
        SCHOOLGRADELEVELID uniqueidentifier,
        ADVISOR nvarchar(200),
        HOMEROOMTEACHER nvarchar(200)
    )
    insert into @SPE
    (
        GRADELEVEL,
        GRADELEVELID,
        SCHOOLGRADELEVELID,
        ADVISOR,
        HOMEROOMTEACHER
    )
    select
        GRADELEVEL.DESCRIPTION,
        GRADELEVEL.ID,
        SCHOOLGRADELEVEL.ID,
        ADVISOR.NAME,
        HOMEROOMTEACHER.NAME
    from dbo.EDUCATIONALHISTORY
        inner join dbo.STUDENTPROGRESSION on EDUCATIONALHISTORY.ID = STUDENTPROGRESSION.ENROLLMENTID
        left join dbo.CONSTITUENT HOMEROOMTEACHER on STUDENTPROGRESSION.HOMEROOMTEACHERID = HOMEROOMTEACHER.ID
        left join dbo.STUDENTADVISOR on STUDENTPROGRESSION.ID = STUDENTADVISOR.STUDENTPROGRESSIONID
        left join dbo.CONSTITUENT ADVISOR on STUDENTADVISOR.FACULTYID = ADVISOR.ID
        inner join dbo.SCHOOLGRADELEVEL on STUDENTPROGRESSION.SCHOOLGRADELEVELID = SCHOOLGRADELEVEL.ID
        inner join dbo.GRADELEVEL on SCHOOLGRADELEVEL.GRADELEVELID = GRADELEVEL.ID
    where EDUCATIONALHISTORY.CONSTITUENTID = @ID
        and not (STUDENTPROGRESSION.ENDDATE < @SESSION_STARTDATE or STUDENTPROGRESSION.STARTDATE > @SESSION_ENDDATE)    

    if (select count(GRADELEVELID) from @SPE where GRADELEVELID is not null) = 1
    begin
        select
            @GRADELEVELID = GRADELEVELID,
            @SCHOOLGRADELEVELID = SCHOOLGRADELEVELID
        from @SPE
        where GRADELEVELID is not null
    end    

    set @GRADELEVEL = null
    select @GRADELEVEL = coalesce(@GRADELEVEL + '; ', '') + GRADELEVEL
    from @SPE
    group by GRADELEVEL
    order by GRADELEVEL

    set @ADVISOR = null
    select @ADVISOR = coalesce(@ADVISOR + '; ', '') + ADVISOR
    from @SPE
    group by ADVISOR
    order by ADVISOR

    set @HOMEROOMTEACHER = null
    select @HOMEROOMTEACHER = coalesce(@HOMEROOMTEACHER + '; ', '') + HOMEROOMTEACHER
    from @SPE
    group by HOMEROOMTEACHER
    order by HOMEROOMTEACHER
end