USP_DATAFORMTEMPLATE_EDIT_COURSERESTRICTION

The save procedure used by the edit dataform template "Course Restriction Edit Data Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@LENGTHINTERMS	int	IN	Terms in length
@STARTTERMS	xml	IN	Starting in terms
@PATTERNID	uniqueidentifier	IN	Pattern
@COURSEGRADINGID	uniqueidentifier	IN
@COURSEGRADINGMARKINGCOLUMNS	xml	IN

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_COURSERESTRICTION (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @LENGTHINTERMS int,
    @STARTTERMS xml,
    @PATTERNID uniqueidentifier,
    @COURSEGRADINGID uniqueidentifier,
    @COURSEGRADINGMARKINGCOLUMNS xml
)
as

    set nocount on;

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    begin try
        -- Determine if the user is attempting to remove a start term which is currently in use

        if exists(
            select 1
            from dbo.COURSERESTRICTIONSTARTTERM
                left join 
                    (
                        select
                            T.c.value('(ID)[1]','uniqueidentifier') as ID,
                            T.c.value('(STARTDATE)[1]','date') as STARTDATE,
                            T.c.value('(ENDDATE)[1]','date') as ENDDATE
                        from @STARTTERMS.nodes('/STARTTERMS/ITEM') as T(c)
                    ) STARTTERMS on
                        COURSERESTRICTIONSTARTTERM.ID = STARTTERMS.ID                    
                inner join dbo.COURSERESTRICTION on
                    COURSERESTRICTIONSTARTTERM.COURSERESTRICTIONID = COURSERESTRICTION.ID
                inner join dbo.CLASS on
                    COURSERESTRICTION.COURSEID = CLASS.COURSEID and
                    COURSERESTRICTIONSTARTTERM.STARTDATE = CLASS.STARTDATE
            where STARTTERMS.ID is null and
                COURSERESTRICTION.ID = @ID
            )
        begin
            raiserror('BBERR_COURSERESTRICTION_DELETE_CHECKFORCLASSES', 13, 1);
        end

        -- handle updating the data

        update dbo.COURSERESTRICTION set
            LENGTHINTERMS = @LENGTHINTERMS,
            PATTERNID = @PATTERNID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where ID = @ID

        exec dbo.USP_COURSERESTRICTION_GETSTARTTERMS_UPDATEFROMXML @ID, @STARTTERMS, @CHANGEAGENTID, @CURRENTDATE;

        -- validate LENGTHINTERMS vs. selected STARTTERMS; these can be edited at either parent or child level, so validating here after both have been saved

        if dbo.UFN_COURSERESTRICTION_LENGTHINTERMS_VALID(@ID, @LENGTHINTERMS) = 0 
            raiserror('CK_COURSERESTRICTION_LENGTHINTERMS_VALID', 13, 1)

        if @COURSEGRADINGID is not null
        begin
            declare @COURSEID uniqueidentifier, @SESSIONSTARTDATE date, @SESSIONENDDATE date
            select @COURSEID = COURSEID, @SESSIONSTARTDATE = STARTDATE, @SESSIONENDDATE = ENDDATE from dbo.COURSERESTRICTION where ID = @ID
            exec dbo.USP_COURSEGRADING_GETMARKINGCOLUMNS_UPDATEFROMXML @COURSEGRADINGID, @COURSEID, @SESSIONSTARTDATE, @SESSIONENDDATE, @COURSEGRADINGMARKINGCOLUMNS, @CHANGEAGENTID, @CURRENTDATE;               
        end
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;