USP_DATALIST_COURSEACADEMICCATALOGDEPARTMENT_BYDEPARTMENT

Returns a list of courses for a given department.

Parameters

Parameter	Parameter Type	Mode	Description
@CONTEXTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.

Definition

Copy



CREATE procedure dbo.USP_DATALIST_COURSEACADEMICCATALOGDEPARTMENT_BYDEPARTMENT(@CONTEXTID uniqueidentifier)
as
    set nocount on;

    select
        COURSEACADEMICCATALOGDEPARTMENT.ID,
        COURSE.ID as COURSEID,
        COURSE.COURSEID as USERCOURSEID,
        COURSE.NAME,
        CONSTITUENT.KEYNAME
    from 
        dbo.ACADEMICCATALOGDEPARTMENT
    -- All courses in same school as department that are "all courses" or "selected courses"

    join dbo.COURSE                                 on (COURSE.SCHOOLID = ACADEMICCATALOGDEPARTMENT.SCHOOLID 
                                                        and COURSE.SELECTEDDEPARTMENTSCODE <> 2)
    left join dbo.COURSEACADEMICCATALOGDEPARTMENT   on (COURSE.SELECTEDDEPARTMENTSCODE = 1 
                                                        and COURSEACADEMICCATALOGDEPARTMENT.COURSEID = COURSE.ID 
                                                        and COURSEACADEMICCATALOGDEPARTMENT.ACADEMICCATALOGDEPARTMENTID = ACADEMICCATALOGDEPARTMENT.ID)
    join dbo.CONSTITUENT                            on CONSTITUENT.ID = COURSE.SCHOOLID
    where 
        ACADEMICCATALOGDEPARTMENT.ID = @CONTEXTID
        -- now take out courses that are "selected courses" but not linked to this dept.

        and not (COURSE.SELECTEDDEPARTMENTSCODE = 1 and COURSEACADEMICCATALOGDEPARTMENT.ID is null)
    order by 
        USERCOURSEID