USP_DATAFORMTEMPLATE_VIEW_STUDENTENROLLMENTDETAILS

The load procedure used by the view dataform template "Student Enrollment Details View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@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.
@SCHOOLNAME	nvarchar(154)	INOUT	School enrolled
@EDUCATIONALREASONLEFT	nvarchar(100)	INOUT	Reason left
@CURRENTSTATUS	nvarchar(100)	INOUT	Current status
@STATUSDATE	date	INOUT	Status date
@STATUSREASON	nvarchar(100)	INOUT	Status reason
@DATESATTENDED	nvarchar(50)	INOUT	Dates attended
@GRADUATIONDATE	UDT_FUZZYDATE	INOUT	Graduation date
@CLASSOF	UDT_YEAR	INOUT	Class of
@STUDENTID	uniqueidentifier	INOUT	STUDENTID
@STUDENTNAME	nvarchar(400)	INOUT	STUDENTNAME
@GRADEENROLLED	nvarchar(100)	INOUT	Grade enrolled
@GRADELEFT	nvarchar(100)	INOUT	Grade left
@ATTRIBUTEDEFINED	bit	INOUT	ATTRIBUTEDEFINED
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.

Definition

Copy



            CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_STUDENTENROLLMENTDETAILS
            (
                @ID uniqueidentifier,
                @DATALOADED bit = 0 output,
                @SCHOOLNAME nvarchar(154) = null output,
                @EDUCATIONALREASONLEFT nvarchar(100) = null output,
                @CURRENTSTATUS nvarchar(100) = null output,
                @STATUSDATE date = null output,
                @STATUSREASON nvarchar(100) = null output,
                @DATESATTENDED nvarchar(50) = null output,
                @GRADUATIONDATE dbo.UDT_FUZZYDATE = null output,
                @CLASSOF dbo.UDT_YEAR = null output,
                @STUDENTID uniqueidentifier = null output,
                @STUDENTNAME nvarchar(400) = null output,
                @GRADEENROLLED nvarchar(100) = null output,
                @GRADELEFT nvarchar(100) = null output,
                @ATTRIBUTEDEFINED bit = null output,
                @CURRENTAPPUSERID uniqueidentifier = null
            )
            as
                set nocount on;

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

                set @DATALOADED = 0;



                -- populate the output parameters, which correspond to fields on the form.  Note that

                -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system

                -- will display a "no data loaded" message.

                select
                    @DATALOADED = 1,
                    @SCHOOLNAME = dbo.CONSTITUENT.[NAME],
                    @EDUCATIONALREASONLEFT = dbo.EDUCATIONALREASONLEFTCODE.[DESCRIPTION],
                    @DATESATTENDED = CONVERT(varchar(10),dbo.UFN_DATE_FROMFUZZYDATE(STARTDATE),101) + CASE DATELEFT WHEN '00000000' THEN ' - Present' ELSE +' - ' + CONVERT(varchar(10),dbo.UFN_DATE_FROMFUZZYDATE(DATELEFT),101) END, 
                    @GRADUATIONDATE = dbo.EDUCATIONALHISTORY.[DATEGRADUATED],
                    @CLASSOF = dbo.EDUCATIONALHISTORY.[CLASSOF],
                    @STUDENTID = CONSTIT_STUDENT.[ID],
                    @STUDENTNAME = CONSTIT_STUDENT.[NAME],
                    @GRADEENROLLED = GRADE_ENROLLED.[DESCRIPTION],
                    @GRADELEFT = GRADE_LEFT.[DESCRIPTION],
                    @ATTRIBUTEDEFINED = dbo.UFN_ATTRIBUTECATEGORY_DEFINEDFORRECORDTYPE_FORAPPUSER('EDUCATIONAL HISTORY', @CURRENTAPPUSERID)
                from dbo.EDUCATIONALHISTORY
                inner join dbo.CONSTITUENT CONSTIT_STUDENT on dbo.EDUCATIONALHISTORY.[CONSTITUENTID] = CONSTIT_STUDENT.[ID]
                inner join dbo.CONSTITUENT on dbo.EDUCATIONALHISTORY.[EDUCATIONALINSTITUTIONID] = dbo.CONSTITUENT.[ID]
                left outer join dbo.EDUCATIONALREASONLEFTCODE on dbo.EDUCATIONALHISTORY.[EDUCATIONALREASONLEFTCODEID] = dbo.EDUCATIONALREASONLEFTCODE.[ID]
                left outer join dbo.GRADELEVEL as GRADE_ENROLLED on dbo.EDUCATIONALHISTORY.[PROGRESSIONGRADELEVELENROLLEDID] = GRADE_ENROLLED.[ID]
                left outer join dbo.GRADELEVEL as GRADE_LEFT on dbo.EDUCATIONALHISTORY.[PROGRESSIONGRADELEVELLEFTID] = GRADE_LEFT.[ID]
                where dbo.EDUCATIONALHISTORY.[ID] = @ID

                select top 1
                    @CURRENTSTATUS = dbo.EDUCATIONALHISTORYSTATUS.[DESCRIPTION], 
                    @STATUSDATE = dbo.EDUCATIONALHISTORYSTATUSHISTORY.[STATUSDATE],
                    @STATUSREASON = dbo.EDUCATIONALHISTORYSTATUSHISTORY.[STATUSREASON]
                from
                    dbo.EDUCATIONALHISTORYSTATUSHISTORY
                inner join dbo.EDUCATIONALHISTORYSTATUS on dbo.EDUCATIONALHISTORYSTATUSHISTORY.[EDUCATIONALHISTORYSTATUSID] = dbo.EDUCATIONALHISTORYSTATUS.[ID]
                where dbo.EDUCATIONALHISTORYSTATUSHISTORY.[EDUCATIONALHISTORYID] = @ID
                order by dbo.EDUCATIONALHISTORYSTATUSHISTORY.[DATECHANGED] desc

                return 0;