USP_DATAFORMTEMPLATE_EDIT_TRANSFERSTUDENT

The save procedure used by the edit dataform template "Transfer Student Data Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	nvarchar(73)	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.
@TRANSFERDATE	date	IN	Transfer date
@TRANSFERTERMID	uniqueidentifier	IN	Transfer starting in
@TRANSFERTO_CLASSID	uniqueidentifier	IN	Transfer to
@EXCEEDTARGETCLASSSIZE	bit	IN	Exceed target class size

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_TRANSFERSTUDENT (
    @ID nvarchar(73),
    @CHANGEAGENTID uniqueidentifier = null,
    @TRANSFERDATE date,
    @TRANSFERTERMID uniqueidentifier,
    @TRANSFERTO_CLASSID uniqueidentifier,
  @EXCEEDTARGETCLASSSIZE bit
)
as

    set nocount on;

    declare @CLASSID uniqueidentifier
    declare @STUDENTID uniqueidentifier

    begin try
        select
            @CLASSID = cast(substring(@ID,1,36) as uniqueidentifier),
            @STUDENTID = cast(substring(@ID,38,36) as uniqueidentifier)

        declare @STUDENTCOURSEID uniqueidentifier
        select
            @STUDENTCOURSEID = STUDENTCOURSE.ID
        from dbo.STUDENTCOURSE
            inner join dbo.STUDENTCLASSMEETINGGROUP on STUDENTCOURSE.ID = STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID
            inner join dbo.CLASSMEETINGGROUP on STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
        where STUDENTCOURSE.STUDENTID = @STUDENTID
            and CLASSMEETINGGROUP.CLASSID = @CLASSID

        declare @TERM_STARTDATE date
        select @TERM_STARTDATE = TERM.STARTDATE
        from dbo.TERM
        where TERM.ID = @TRANSFERTERMID    

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

        declare @CURRENTDATE datetime = getdate()

        update dbo.STUDENTCLASSMEETINGGROUP set
            STUDENTCLASSMEETINGGROUP.STATUSCODE = 3,
            STUDENTCLASSMEETINGGROUP.STATUSDATE = @TRANSFERDATE,
            STUDENTCLASSMEETINGGROUP.CHANGEDBYID = @CHANGEAGENTID,
            STUDENTCLASSMEETINGGROUP.DATECHANGED = @CURRENTDATE
        from dbo.STUDENTCOURSE
            inner join dbo.STUDENTCLASSMEETINGGROUP on STUDENTCOURSE.ID = STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID
            inner join dbo.CLASSMEETINGGROUP on STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
        where STUDENTCOURSE.STUDENTID = @STUDENTID
            and CLASSMEETINGGROUP.CLASSID = @CLASSID
            and CLASSMEETINGGROUP.STARTDATE >= @TERM_STARTDATE
            and STUDENTCLASSMEETINGGROUP.STATUSCODE = 0

        exec dbo.USP_STUDENTCLASS_ADD @STUDENTID, @TRANSFERTO_CLASSID, @EXCEEDTARGETCLASSSIZE, @CHANGEAGENTID, @STUDENTCOURSEID, @TRANSFERTERMID

        exec dbo.USP_STUDENTMARKINGCOLUMN_CLEARBLANKGRADES @STUDENTCOURSEID, @CHANGEAGENTID
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;