USP_ENROLLGROUPINCLASS_STUDENTSTOADD_FROMSELECTION

CREATE procedure dbo.USP_ENROLLGROUPINCLASS_STUDENTSTOADD_FROMSELECTION
(
    @STUDENTSELECTIONID 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

    declare @STUDENTS table
    (
        ID uniqueidentifier
    )
    insert into @STUDENTS (ID)
    exec dbo.USP_STUDENT_GETSTUDENTSELECTION @STUDENTSELECTIONID

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