USP_ASSIGNHOMEROOMS_GETSTUDENTS

CREATE procedure dbo.USP_ASSIGNHOMEROOMS_GETSTUDENTS
(
    @SCHOOLGRADELEVELID uniqueidentifier,
    @ACADEMICYEARID uniqueidentifier
)
as
begin
    set nocount on

    select STUDENTPROGRESSION.ID as STUDENTPROGRESSIONID, 
        CONSTITUENT.NAME + CASE WHEN LEN(CONSTITUENT.NICKNAME) > 0 THEN ' (' + CONSTITUENT.NICKNAME + ')' ELSE '' END as NAME,
        CONSTITUENT.GENDER
    from dbo.CONSTITUENT
        inner join dbo.EDUCATIONALHISTORY on CONSTITUENT.ID = EDUCATIONALHISTORY.CONSTITUENTID
        inner join dbo.STUDENTPROGRESSION on EDUCATIONALHISTORY.ID = STUDENTPROGRESSION.ENROLLMENTID
        inner join dbo.SCHOOLGRADELEVEL on STUDENTPROGRESSION.SCHOOLGRADELEVELID = SCHOOLGRADELEVEL.ID
    where dbo.UFN_ACADEMICYEAR_GET_FORSCHOOL_BYDATE(SCHOOLGRADELEVEL.SCHOOLID, STUDENTPROGRESSION.STARTDATE, STUDENTPROGRESSION.ENDDATE) = @ACADEMICYEARID
        and STUDENTPROGRESSION.SCHOOLGRADELEVELID = @SCHOOLGRADELEVELID
    order by CONSTITUENT.KEYNAME
end