USP_SEARCHLIST_STUDENTSKILL

CREATE procedure dbo.USP_SEARCHLIST_STUDENTSKILL
(
    @NAME nvarchar(400) = null,
    @SCHOOLID uniqueidentifier = null,
    @GRADELEVELID uniqueidentifier = null,
    @COURSENAME nvarchar(60) = null,
    @MAXROWS smallint = 500
)
as
    set @NAME = COALESCE(@NAME,'') + '%' ;

    select top(@MAXROWS)
        STUDENTSKILL.ID,
        STUDENTSKILL.NAME as SKILLNAME,
        STUDENTSKILLRATINGGROUP.NAME AS RATINGGROUPNAME,
        case 
            when STUDENTSKILLSCHOOL.ID is not null then CONSTITUENT.KEYNAME
            when STUDENTSKILLGRADELEVEL.ID is not null then GRADELEVEL.DESCRIPTION
            when STUDENTSKILLCOURSE.ID is not null then COURSE.NAME
        end as APPLIEDTO,
        null,--STUDENTSKILL.STARTDATE,

        null--STUDENTSKILL.ENDDATE


    from 
        dbo.STUDENTSKILL

    join dbo.STUDENTSKILLRATINGGROUP on STUDENTSKILLRATINGGROUP.ID = STUDENTSKILL.STUDENTSKILLRATINGGROUPID

    left join dbo.STUDENTSKILLSCHOOL on STUDENTSKILLSCHOOL.STUDENTSKILLID = STUDENTSKILL.ID
    left join dbo.CONSTITUENT on CONSTITUENT.ID = STUDENTSKILLSCHOOL.SCHOOLID

    left join dbo.STUDENTSKILLGRADELEVEL on STUDENTSKILLGRADELEVEL.STUDENTSKILLID = STUDENTSKILL.ID
    left join dbo.GRADELEVEL on GRADELEVEL.ID = STUDENTSKILLGRADELEVEL.GRADELEVELID

    left join dbo.STUDENTSKILLCOURSE on STUDENTSKILLCOURSE.STUDENTSKILLID = STUDENTSKILL.ID
    left join dbo.COURSE on COURSE.ID = STUDENTSKILLCOURSE.COURSEID

    where
         (STUDENTSKILL.NAME LIKE @NAME)
         and (STUDENTSKILLSCHOOL.SCHOOLID = @SCHOOLID or @SCHOOLID is null)
         and (STUDENTSKILLGRADELEVEL.GRADELEVELID = @GRADELEVELID or @GRADELEVELID is null)
         and (COURSE.NAME LIKE @COURSENAME + '%' or @COURSENAME is null)
    order by 
        STUDENTSKILL.NAME asc