USP_PROMOTESTUDENTS_GETPROMOTIONSCHEDULEENTRIES

CREATE procedure dbo.USP_PROMOTESTUDENTS_GETPROMOTIONSCHEDULEENTRIES
(
    @PROMOTIONSCHEDULEID uniqueidentifier
)
as
begin
    set nocount on

    select distinct
        case GRADUATES when 0 then SGL.ID else GRAD_SGL.ID end NEXTSCHOOLGRADELEVELID,
        dbo.UFN_GRADELEVEL_GETABBREVIATIONANDDESCRIPTION(case GRADUATES when 0 then SGL.GRADELEVELID else GRAD_SGL.GRADELEVELID end) as GRADELEVEL,
        case GRADUATES when 0 then GL.SEQUENCE else GRAD_GL.SEQUENCE end SEQUENCE
    from dbo.PROMOTIONSCHEDULEENTRY
        inner join dbo.PROMOTIONSCHEDULE on PROMOTIONSCHEDULEENTRY.PROMOTIONSCHEDULEID = PROMOTIONSCHEDULE.ID
        left outer join dbo.SCHOOLGRADELEVEL SGL on PROMOTIONSCHEDULEENTRY.NEXTSCHOOLGRADELEVELID = SGL.ID
        left outer join dbo.GRADELEVEL GL on SGL.GRADELEVELID = GL.ID
        left outer join dbo.SCHOOLGRADELEVEL GRAD_SGL on PROMOTIONSCHEDULE.NEXTSCHOOLGRADELEVELID = GRAD_SGL.ID
        left outer join dbo.GRADELEVEL GRAD_GL on GRAD_SGL.GRADELEVELID = GRAD_GL.ID
    where PROMOTIONSCHEDULEID = @PROMOTIONSCHEDULEID
        and (SGL.GRADELEVELID is not null or GRAD_SGL.GRADELEVELID is not null)
    order by 3
end