USP_ENROLLGROUPINCLASS_STUDENTSTOADD_FROMHOMEROOMTEACHER

CREATE procedure dbo.USP_ENROLLGROUPINCLASS_STUDENTSTOADD_FROMHOMEROOMTEACHER
(
    @FACULTYID as uniqueidentifier,
    @CLASSID as uniqueidentifier
)
as
begin
    declare @STARTDATE date
    declare @ENDDATE date
    select
        @STARTDATE = CLASS.STARTDATE,
        @ENDDATE = CLASS.ENDDATE
    from dbo.CLASS
    where CLASS.ID = @CLASSID

    select
        EDUCATIONALHISTORY.CONSTITUENTID as ID,
        CONSTITUENT.NAME,
        CONSTITUENT.GENDER
    from dbo.STUDENTPROGRESSION
        inner join dbo.EDUCATIONALHISTORY on STUDENTPROGRESSION.ENROLLMENTID = EDUCATIONALHISTORY.ID
        inner join dbo.CONSTITUENT on EDUCATIONALHISTORY.CONSTITUENTID = CONSTITUENT.ID
    where STUDENTPROGRESSION.HOMEROOMTEACHERID = @FACULTYID
        and STUDENTPROGRESSION.STARTDATE <= @STARTDATE
        and STUDENTPROGRESSION.ENDDATE >= @ENDDATE
    group by EDUCATIONALHISTORY.CONSTITUENTID, CONSTITUENT.NAME, CONSTITUENT.GENDER
    order by CONSTITUENT.NAME asc
end