USP_SEARCHLIST_COURSE_SHARED

            CREATE procedure dbo.USP_SEARCHLIST_COURSE_SHARED
            (
                @NAME nvarchar(60) = null,
                @COURSEID nvarchar(12) = null,
                @SCHOOLID uniqueidentifier = null,
                @GRADELEVELID uniqueidentifier = null,
                @ISINACTIVE bit = null,
                @MAXROWS smallint = 500,
                @SESSIONID uniqueidentifier = null,
                @CHECKCOURSEGRADING bit = 0,
                @CHECKCOURSERESTRICTION bit = 0,
                @STARTTERMID uniqueidentifier = null
            )
            as
                set nocount on;

                declare @SESSION_STARTDATE date
                declare @SESSION_ENDDATE date

                if @SESSIONID is not null
                begin
                    select
                        @SESSION_STARTDATE = min(STARTDATE),
                        @SESSION_ENDDATE = max(ENDDATE)
                    from dbo.TERM
                    where TERM.SESSIONID = @SESSIONID
                end

                select distinct top(@MAXROWS) 
                    COURSE.ID,
                    COURSE.COURSEID,
                    COURSE.NAME,
                    CONSTITUENT.KEYNAME as SCHOOL,
                    dbo.UFN_COURSE_GETGRADELEVELSTRING(COURSE.ID) 'GRADE',
                    COURSE.ISINACTIVE
                from dbo.COURSE
                    inner join dbo.CONSTITUENT on CONSTITUENT.ID = COURSE.SCHOOLID
                    left join dbo.COURSEGRADELEVEL on COURSE.ID = COURSEGRADELEVEL.COURSEID
                    left join dbo.SCHOOLGRADELEVEL on COURSEGRADELEVEL.SCHOOLGRADELEVELID = SCHOOLGRADELEVEL.ID
                where 
                    (@COURSEID is null or (dbo.COURSE.COURSEID like @COURSEID + '%')) and
                    (@NAME is null or (dbo.COURSE.NAME like '%' + @NAME + '%')) and
                    (@SCHOOLID is null or (dbo.COURSE.SCHOOLID = @SCHOOLID)) and
                    (@GRADELEVELID is null or (dbo.SCHOOLGRADELEVEL.ID = @GRADELEVELID)) and
                    (@ISINACTIVE is null or (dbo.COURSE.ISINACTIVE = @ISINACTIVE)) and
                    (@SESSIONID is null or @CHECKCOURSERESTRICTION = 0 
                        or exists
                            (select 1
                            from dbo.V_COURSERESTRICTION as COURSERESTRICTION
                                inner join dbo.COURSERESTRICTIONSTARTTERM on COURSERESTRICTION.ID = COURSERESTRICTIONSTARTTERM.COURSERESTRICTIONID
                                inner join dbo.TERM on
                                    COURSERESTRICTIONSTARTTERM.STARTDATE = TERM.STARTDATE and
                                    COURSERESTRICTIONSTARTTERM.ENDDATE = TERM.ENDDATE
                            where dbo.COURSE.ID = COURSERESTRICTION.COURSEID and
                                COURSERESTRICTION.STARTDATE >= @SESSION_STARTDATE and
                                COURSERESTRICTION.ENDDATE <= @SESSION_ENDDATE and
                                (@STARTTERMID is null or TERM.ID = @STARTTERMID))
                    ) and
                    (@SESSIONID is null or @CHECKCOURSEGRADING = 0
                        or exists
                            (select 1
                            from dbo.V_COURSEGRADING as COURSEGRADING
                            where dbo.COURSE.ID = COURSEGRADING.COURSEID and
                                COURSEGRADING.STARTDATE >= @SESSION_STARTDATE and
                                COURSEGRADING.ENDDATE <= @SESSION_ENDDATE)
                    )
                order by
                    COURSE.COURSEID