USP_DATAFORMTEMPLATE_VIEW_COURSE_CURRENTTERM

The load procedure used by the view dataform template "Course Current Term View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@SCHOOLID	uniqueidentifier	INOUT	SCHOOLID
@ACADEMICYEARID	uniqueidentifier	INOUT	ACADEMICYEARID
@SESSIONID	uniqueidentifier	INOUT	SESSIONID
@TERMID	uniqueidentifier	INOUT	TERMID

Definition

Copy



create procedure dbo.USP_DATAFORMTEMPLATE_VIEW_COURSE_CURRENTTERM
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @SCHOOLID uniqueidentifier = null output,
    @ACADEMICYEARID uniqueidentifier = null output,
    @SESSIONID uniqueidentifier = null output,
    @TERMID uniqueidentifier = null output
)
as
    set nocount on;

    set @DATALOADED = 1;

    declare @CURRENTDATE date = getdate()

    -- populate the output parameters, which correspond to fields on the form.  Note that

    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system

    -- will display a "no data loaded" message.

    select top (1)
        @SCHOOLID = COURSE.SCHOOLID,
        @ACADEMICYEARID = ACADEMICYEAR.ID,
        @SESSIONID =
            case when TERM.ID is not null
                then SESSION.ID
                else null
            end,           
        @TERMID = TERM.ID
    from dbo.COURSE
        left join dbo.ACADEMICYEAR on COURSE.SCHOOLID = ACADEMICYEAR.SCHOOLID
            and @CURRENTDATE >= ACADEMICYEAR.STARTDATE
            and @CURRENTDATE <= ACADEMICYEAR.ENDDATE 
        left join dbo.SESSION on ACADEMICYEAR.ID = SESSION.ACADEMICYEARID
        left join dbo.TERM on SESSION.ID = TERM.SESSIONID
            and @CURRENTDATE >= TERM.STARTDATE
            and @CURRENTDATE <= TERM.ENDDATE
    where COURSE.ID = @ID
    order by TERM.STARTDATE desc

    return 0;