USP_DATALIST_STUDENT_CLASS

CREATE procedure dbo.USP_DATALIST_STUDENT_CLASS
    (
        @CLASSID uniqueidentifier,
        @STATUSCODE tinyint = null
    )
    as
        set nocount on;

        select
            cast(@CLASSID as nvarchar(36)) + '|' + cast(CONSTITUENT.ID as nvarchar(36)) as ID,
            dbo.CONSTITUENT.ID AS STUDENTID,
            CONSTITUENT.NAME,                    
            dbo.UFN_CLASS_GETSTUDENTGRADELEVELSTRING(@CLASSID, STUDENTCOURSE.ID) as GRADELEVEL,
            CONSTITUENT.GENDER,
            dbo.UFN_CLASS_GETSTUDENTTERMSTRING(@CLASSID, STUDENTCOURSE.ID) as TERMS,
            case 
                when STUDENT_CLASS.STATUSCODE <> 0 then
                    STUDENT_CLASS.STATUS
                else '' end as STATUS,
            STUDENT_CLASS.STATUSCODE,
            STUDENT_CLASS.STATUSDATE,
            STUDENT_CLASS.WITHDRAWALDATE
        from dbo.UFN_STUDENT_CLASS(@CLASSID) as STUDENT_CLASS
            inner join dbo.STUDENTCOURSE on STUDENT_CLASS.STUDENTCOURSEID = STUDENTCOURSE.ID
            inner join dbo.CONSTITUENT on dbo.STUDENTCOURSE.STUDENTID = dbo.CONSTITUENT.ID
        where 
            (@STATUSCODE is null or @STATUSCODE = STUDENT_CLASS.STATUSCODE)
        order by CONSTITUENT.[NAME]