USP_DATAFORMTEMPLATE_VIEW_STUDENTPROGRESSIONPROFILE

The load procedure used by the view dataform template "Student Progression Profile View"

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@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.
@STUDENTNAME	nvarchar(154)	INOUT	Student name
@NICKNAME	nvarchar(50)	INOUT	Nickname
@ADVISORS	nvarchar(max)	INOUT	Advisors
@GRADELEVELS	nvarchar(max)	INOUT	Grade levels

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_STUDENTPROGRESSIONPROFILE
(
    @CURRENTAPPUSERID uniqueidentifier,
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @STUDENTNAME nvarchar(154) = null output,
    @NICKNAME nvarchar(50) = null output,
    @ADVISORS nvarchar(max) = null output,
    @GRADELEVELS nvarchar(max) = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows

    set @DATALOADED = 0;

    declare @SCHOOLID uniqueidentifier, @ACADEMICYEARSTARTDATE date, @ACADEMICYEARENDDATE date

    select @SCHOOLID = ACADEMICYEAR.SCHOOLID, @ACADEMICYEARSTARTDATE = ACADEMICYEAR.STARTDATE, @ACADEMICYEARENDDATE = ACADEMICYEAR.ENDDATE
    from dbo.ACADEMICYEAR
    inner join dbo.SESSION on ACADEMICYEAR.ID = SESSION.ACADEMICYEARID
    inner join dbo.GRADINGAPPUSERSESSION on SESSION.ID = GRADINGAPPUSERSESSION.SESSIONID
    where GRADINGAPPUSERSESSION.ID = @CURRENTAPPUSERID

    select @DATALOADED = 1,
           @STUDENTNAME = CONSTITUENT.NAME,
           @NICKNAME = CONSTITUENT.NICKNAME,
           @ADVISORS = dbo.UDA_BUILDLIST(DISTINCT FACULTYNAME.NAME),
           @GRADELEVELS = dbo.UDA_BUILDLIST(DISTINCT GRADELEVEL.DESCRIPTION)
    from dbo.CONSTITUENT
    inner join dbo.EDUCATIONALHISTORY on CONSTITUENT.ID = EDUCATIONALHISTORY.CONSTITUENTID
    inner join dbo.STUDENTPROGRESSION on EDUCATIONALHISTORY.ID = STUDENTPROGRESSION.ENROLLMENTID and STUDENTPROGRESSION.STARTDATE = @ACADEMICYEARSTARTDATE and STUDENTPROGRESSION.ENDDATE = @ACADEMICYEARENDDATE
    inner join dbo.SCHOOLGRADELEVEL on STUDENTPROGRESSION.SCHOOLGRADELEVELID = SCHOOLGRADELEVEL.ID and @SCHOOLID = SCHOOLGRADELEVEL.SCHOOLID
    inner join dbo.GRADELEVEL on SCHOOLGRADELEVEL.GRADELEVELID = GRADELEVEL.ID
    left join dbo.STUDENTADVISOR on STUDENTPROGRESSION.ID = STUDENTADVISOR.STUDENTPROGRESSIONID
    left join dbo.CONSTITUENT FACULTYNAME on STUDENTADVISOR.FACULTYID = FACULTYNAME.ID
    where CONSTITUENT.ID = @ID
    group by CONSTITUENT.ID, CONSTITUENT.NAME, CONSTITUENT.NICKNAME

    return 0;