USP_DATALIST_STUDENTSKILL

CREATE procedure dbo.USP_DATALIST_STUDENTSKILL
(
    @ACADEMICYEARNAMECODEID uniqueidentifier = null,
    @STUDENTSKILLCATEGORYCODEID uniqueidentifier = null,
    @GRADELEVELID uniqueidentifier = null
)
as
    set nocount on;

    select STUDENTSKILL.ID,
           STUDENTSKILLCATEGORYCODE.DESCRIPTION 'CATEGORY',
           STUDENTSKILL.NAME 'SKILLNAME',
           STUDENTSKILLRATINGGROUP.NAME 'RATINGTABLENAME',

           (select stuff(( select '; ' + COURSE.COURSEID
                   from dbo.STUDENTSKILLCOURSE
                       join dbo.COURSE on COURSE.ID = STUDENTSKILLCOURSE.COURSEID
                   where STUDENTSKILLCOURSE.STUDENTSKILLID = STUDENTSKILL.ID
                   order by COURSE.COURSEID
                   for xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '')) 'COURSE'

    from dbo.STUDENTSKILL
        inner join dbo.STUDENTSKILLRATINGGROUP  on STUDENTSKILL.STUDENTSKILLRATINGGROUPID = STUDENTSKILLRATINGGROUP.ID
        left join dbo.STUDENTSKILLCATEGORYCODE  on STUDENTSKILL.STUDENTSKILLCATEGORYCODEID = STUDENTSKILLCATEGORYCODE.ID        
        left join dbo.STUDENTSKILLCOURSE        on STUDENTSKILL.ID = STUDENTSKILLCOURSE.STUDENTSKILLID
        left join dbo.COURSE                    on STUDENTSKILLCOURSE.COURSEID = COURSE.ID       
        left join dbo.ACADEMICYEAR              on (@ACADEMICYEARNAMECODEID is not null) and
                                                    (ACADEMICYEAR.STARTDATE >= STUDENTSKILLCOURSE.STARTDATE) and 
                                                    ((ACADEMICYEAR.ENDDATE <= STUDENTSKILLCOURSE.ENDDATE) or (STUDENTSKILLCOURSE.ENDDATE is null)) and
                                                    (ACADEMICYEAR.SCHOOLID = COURSE.SCHOOLID)        
    where 
        ((@STUDENTSKILLCATEGORYCODEID is null) or (@STUDENTSKILLCATEGORYCODEID = STUDENTSKILLCATEGORYCODE.ID)) and
        ((@GRADELEVELID is null) or 
            (@GRADELEVELID in 
                (select SCHOOLGRADELEVEL.GRADELEVELID from dbo.SCHOOLGRADELEVEL
                inner join dbo.COURSEGRADELEVEL on SCHOOLGRADELEVEL.ID = COURSEGRADELEVEL.SCHOOLGRADELEVELID
                where COURSEGRADELEVEL.COURSEID = STUDENTSKILLCOURSE.COURSEID))) and
        ((@ACADEMICYEARNAMECODEID is null) or
            (ACADEMICYEAR.ACADEMICYEARNAMECODEID = @ACADEMICYEARNAMECODEID))
    group by STUDENTSKILL.ID, STUDENTSKILLCATEGORYCODE.DESCRIPTION, STUDENTSKILL.NAME, STUDENTSKILLRATINGGROUP.NAME
    order by STUDENTSKILLCATEGORYCODE.DESCRIPTION, STUDENTSKILL.NAME