USP_DATAFORMTEMPLATE_ADD_COURSESTUDENTSKILL

The save procedure used by the add dataform template "Course Student Skill Add Data Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@COPYSTUDENTSKILLID	uniqueidentifier	IN	Copy existing skill
@COURSEID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@STUDENTSKILLRATINGGROUPID	uniqueidentifier	IN	Ratings table
@COURSESEARCHID	uniqueidentifier	IN	Add to course
@STARTINACADEMICYEARNAMECODEID	uniqueidentifier	IN	Start in
@STOPAFTERACADEMICYEARNAMECODEID	uniqueidentifier	IN	Stop after
@STUDENTSKILLCOURSEMARKINGCOLUMNS	xml	IN	Marking Columns
@STUDENTSKILLS	xml	IN	Skills

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_COURSESTUDENTSKILL
(
    @ID uniqueidentifier = null output,
    @COPYSTUDENTSKILLID uniqueidentifier = null,
    @COURSEID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @STUDENTSKILLRATINGGROUPID uniqueidentifier,
    @COURSESEARCHID uniqueidentifier = null,
    @STARTINACADEMICYEARNAMECODEID uniqueidentifier,
    @STOPAFTERACADEMICYEARNAMECODEID uniqueidentifier = null,
    @STUDENTSKILLCOURSEMARKINGCOLUMNS xml,
    @STUDENTSKILLS 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 @SKILLCOURSE table(STUDENTSKILLCOURSEID uniqueidentifier, STUDENTSKILLID uniqueidentifier, 
                                NAME nvarchar(400), STUDENTSKILLCATEGORYCODEID uniqueidentifier, 
                                SEQUENCE integer primary key identity)
    declare @SKILLCOURSEMC table(ID uniqueidentifier, SKILLCOURSEID uniqueidentifier, MCID uniqueidentifier)

    insert into @SKILLCOURSE
        (STUDENTSKILLCOURSEID, STUDENTSKILLID, NAME, STUDENTSKILLCATEGORYCODEID)
    select
        newid(),
        newid(),        
        T.c.value('(NAME)[1]','nvarchar(400)') AS 'NAME',
        T.c.value('(STUDENTSKILLCATEGORYCODEID)[1]','uniqueidentifier') AS 'STUDENTSKILLCATEGORYCODEID'
    from @STUDENTSKILLS.nodes('/STUDENTSKILLS/ITEM') T(c)      

    -- handle inserting the data

    insert into dbo.STUDENTSKILL
        (ID, STUDENTSKILLRATINGGROUPID, NAME, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, STUDENTSKILLCATEGORYCODEID)
    select
        SC.STUDENTSKILLID,
        @STUDENTSKILLRATINGGROUPID,
        SC.NAME,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE,
        SC.STUDENTSKILLCATEGORYCODEID
    from
        @SKILLCOURSE SC

    --If a contextID is not passed in, course search field will replace the course label

    if @COURSEID = '00000000-0000-0000-0000-000000000000'
    begin
        if (@COURSESEARCHID is not null) and (@COURSESEARCHID <> '00000000-0000-0000-0000-000000000000')
            set @COURSEID = @COURSESEARCHID
        else
        begin
            raiserror('BBERR_STUDENTSKILLCOURSE_COURSEIDNOTFOUND', 13, 1);
            return 1;            
        end
    end

    -- add a skill course record for all the skills

    if exists(select STUDENTSKILLID from @SKILLCOURSE)
    begin                    
        --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 = @COURSEID

        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 = @COURSEID

        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)

        declare @MAXSEQUENCE integer
        set @MAXSEQUENCE = (select coalesce(max(SSC.SEQUENCE), -1) from dbo.STUDENTSKILLCOURSE SSC where SSC.COURSEID=@COURSEID) 

        insert into dbo.STUDENTSKILLCOURSE
            (ID, COURSEID, STUDENTSKILLID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, STARTDATE, ENDDATE, SEQUENCE)
        select
            SC.STUDENTSKILLCOURSEID,
            @COURSEID,
            SC.STUDENTSKILLID,
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE, 
            @CURRENTDATE,
            @STARTDATE, 
            @ENDDATE,
            @MAXSEQUENCE + SC.SEQUENCE
        from @SKILLCOURSE SC

        insert into dbo.STUDENTSKILLCOURSEMARKINGCOLUMN
            (ID, STUDENTSKILLCOURSEID, MARKINGCOLUMNDESCRIPTIONCODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        select
            newid(),
            SC.STUDENTSKILLCOURSEID,
            c.value('MARKINGCOLUMNDESCRIPTIONCODEID[1]', 'uniqueidentifier'),
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE, 
            @CURRENTDATE
        from @SKILLCOURSE SC
            cross join @STUDENTSKILLCOURSEMARKINGCOLUMNS.nodes('/STUDENTSKILLCOURSEMARKINGCOLUMNS/ITEM') as T(c)
    end
    else        
        raiserror('BBERR_STUDENTSKILLCOURSE_SKILLS_REQUIRED', 13, 1)

end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0