USP_DATAFORMTEMPLATE_SAVE_COURSESTUDENTSKILL

The save procedure used by the edit dataform template "Course Student Skill 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.
@NAME	nvarchar(400)	IN	Name
@STUDENTSKILLCATEGORYCODEID	uniqueidentifier	IN	Category
@STUDENTSKILLRATINGGROUPID	uniqueidentifier	IN	Ratings table
@STARTINACADEMICYEARNAMECODEID	uniqueidentifier	IN	Start in
@STOPAFTERACADEMICYEARNAMECODEID	uniqueidentifier	IN	Stop after
@STUDENTSKILLCOURSEMARKINGCOLUMNS	xml	IN	Marking Columns

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_SAVE_COURSESTUDENTSKILL (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @NAME nvarchar(400),
    @STUDENTSKILLCATEGORYCODEID uniqueidentifier,
    @STUDENTSKILLRATINGGROUPID uniqueidentifier,    
    @STARTINACADEMICYEARNAMECODEID uniqueidentifier,
    @STOPAFTERACADEMICYEARNAMECODEID uniqueidentifier,
    @STUDENTSKILLCOURSEMARKINGCOLUMNS xml
)
as

    set nocount on;

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    begin try
        declare @STUDENTSKILLID uniqueidentifier
        select @STUDENTSKILLID = STUDENTSKILLID from dbo.STUDENTSKILLCOURSE where ID = @ID

        -- update the shared skill

        update dbo.STUDENTSKILL set
            NAME = @NAME, 
            STUDENTSKILLCATEGORYCODEID = @STUDENTSKILLCATEGORYCODEID,
            STUDENTSKILLRATINGGROUPID = @STUDENTSKILLRATINGGROUPID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = getdate()
        where ID = @STUDENTSKILLID

        --Get the dates

        declare @STARTDATE datetime = null, @ENDDATE datetime = null

        if @STARTINACADEMICYEARNAMECODEID is not null
            select @STARTDATE = ACADEMICYEAR.STARTDATE from dbo.ACADEMICYEAR 
            inner join dbo.COURSE on ACADEMICYEAR.SCHOOLID = COURSE.SCHOOLID
            where ACADEMICYEAR.ACADEMICYEARNAMECODEID = @STARTINACADEMICYEARNAMECODEID and
                COURSE.ID = (select STUDENTSKILLCOURSE.COURSEID from dbo.STUDENTSKILLCOURSE where STUDENTSKILLCOURSE.ID = @ID)

        if @STOPAFTERACADEMICYEARNAMECODEID is not null
            select @ENDDATE = ACADEMICYEAR.ENDDATE from dbo.ACADEMICYEAR 
            inner join dbo.COURSE on ACADEMICYEAR.SCHOOLID = COURSE.SCHOOLID
            where ACADEMICYEAR.ACADEMICYEARNAMECODEID = @STOPAFTERACADEMICYEARNAMECODEID and
                COURSE.ID = (select STUDENTSKILLCOURSE.COURSEID from dbo.STUDENTSKILLCOURSE where STUDENTSKILLCOURSE.ID = @ID)

        if (@STARTDATE is null) 
            raiserror('BBERR_STUDENTSKILLCOURSE_STARTDATE_ACADEMICYEARNOTAVAILABLE', 13, 1)

        if ((@ENDDATE is null) and (@STOPAFTERACADEMICYEARNAMECODEID is not null))
            raiserror('BBERR_STUDENTSKILLCOURSE_ENDDATE_ACADEMICYEARNOTAVAILABLE', 13, 1)

        -- update the info specific to this course

        update dbo.STUDENTSKILLCOURSE set
            STARTDATE = @STARTDATE,
            ENDDATE = @ENDDATE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = getdate()
        where ID = @ID

        -- update marking columns

        exec dbo.USP_STUDENTSKILLCOURSE_GETMARKINGCOLUMN_UPDATEFROMXML @ID, @STUDENTSKILLCOURSEMARKINGCOLUMNS, @CHANGEAGENTID

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;