USP_DATAFORMTEMPLATE_VIEW_BBNCCONSTITEDUCATIONALHISTORYDATA

The load procedure used by the view dataform template "NetCommunity Constituent Educational History Data 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.
@EDUCATIONALINSTITUTION	nvarchar(100)	INOUT	EDUCATIONALINSTITUTION
@STATUS	nvarchar(100)	INOUT	STATUS
@PROGRAM	nvarchar(100)	INOUT	PROGRAM
@DEGREE	nvarchar(100)	INOUT	DEGREE
@HONORAWARDED	nvarchar(100)	INOUT	HONORAWARDED
@COMMENTS	nvarchar(500)	INOUT	COMMENTS
@STARTDATE	UDT_FUZZYDATE	INOUT	STARTDATE
@ENDDATE	UDT_FUZZYDATE	INOUT	ENDDATE
@GRADUATIONDATE	UDT_FUZZYDATE	INOUT	GRADUATIONDATE
@CLASSOF	smallint	INOUT	CLASSOF
@PREFERREDCLASSOF	smallint	INOUT	PREFERREDCLASSOF
@COLLEGE	nvarchar(100)	INOUT	COLLEGE
@DIVISION	nvarchar(100)	INOUT	DIVISION
@DEPARTMENT	nvarchar(100)	INOUT	DEPARTMENT
@SUBDEPARTMENT	nvarchar(100)	INOUT	SUBDEPARTMENT
@DEGREETYPE	nvarchar(100)	INOUT	DEGREETYPE
@ISPRIMARY	bit	INOUT	ISPRIMARY

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_BBNCCONSTITEDUCATIONALHISTORYDATA
                (
          @ID uniqueidentifier,
          @DATALOADED bit = 0 output,
          @EDUCATIONALINSTITUTION nvarchar(100) = null output,
          @STATUS nvarchar(100) = null output,
          @PROGRAM nvarchar(100) = null output,
          @DEGREE nvarchar(100) = null output,
          @HONORAWARDED nvarchar(100) = null output,
          @COMMENTS nvarchar(500) = null output,
          @STARTDATE dbo.UDT_FUZZYDATE = null output,
          @ENDDATE dbo.UDT_FUZZYDATE = null output,
          @GRADUATIONDATE dbo.UDT_FUZZYDATE = null output,
          @CLASSOF smallint = null output,
          @PREFERREDCLASSOF smallint = null output,
          @COLLEGE nvarchar(100) = null output,
          @DIVISION nvarchar(100) = null output,
          @DEPARTMENT nvarchar(100) = null output,
          @SUBDEPARTMENT nvarchar(100) = null output,
          @DEGREETYPE nvarchar(100) = null output,
          @ISPRIMARY bit = null output
                )
        as
        set nocount on;
        set @DATALOADED = 0;

        declare @T table(
          EDUCATIONALINSTITUTION nvarchar(100), 
          STATUS nvarchar(100),
          PROGRAM nvarchar(100),
          DEGREE nvarchar(100),
          HONORAWARDED nvarchar(100),
          COMMENTS nvarchar(500),
          STARTDATE dbo.UDT_FUZZYDATE,
          ENDDATE dbo.UDT_FUZZYDATE,
          GRADUATIONDATE dbo.UDT_FUZZYDATE,
          CLASSOF smallint,
          PREFERREDCLASSOF smallint,
          COLLEGE nvarchar(100),
          DIVISION nvarchar(100),
          DEPARTMENT nvarchar(100),
          SUBDEPARTMENT nvarchar(100),
          DEGREETYPE nvarchar(100),
          ISPRIMARY bit,
          DATEADDED datetime
        );

        insert into @T 
        (
          EDUCATIONALINSTITUTION, 
          STATUS,
          PROGRAM,
          DEGREE,
          HONORAWARDED,
          COMMENTS,
          STARTDATE,
          ENDDATE,
          GRADUATIONDATE,
          CLASSOF,
          PREFERREDCLASSOF,
          COLLEGE,
          DIVISION,
          DEPARTMENT,
          SUBDEPARTMENT,
          DEGREETYPE,
          ISPRIMARY,
          DATEADDED
        )
        select
          EDUCATIONALINSTITUTION.NAME,
          EDUCATIONALHISTORYSTATUS.DESCRIPTION,
          EDUCATIONALPROGRAMCODE.DESCRIPTION,
          EDUCATIONALDEGREECODE.DESCRIPTION,
          EDUCATIONALAWARDCODE.DESCRIPTION,
          EDUCATIONALHISTORY.COMMENT,
          EDUCATIONALHISTORY.STARTDATE,
          EDUCATIONALHISTORY.DATELEFT,
          EDUCATIONALHISTORY.DATEGRADUATED,
          EDUCATIONALHISTORY.CLASSOF,
          EDUCATIONALHISTORY.PREFERREDCLASSYEAR,
          EDUCATIONALCOLLEGECODE.DESCRIPTION,
          EDUCATIONALDIVISIONCODE.DESCRIPTION,
          EDUCATIONALDEPARTMENTCODE.DESCRIPTION,
          EDUCATIONALSUBDEPARTMENTCODE.DESCRIPTION,
          EDUCATIONALDEGREETYPECODE.DESCRIPTION,
          EDUCATIONALHISTORY.ISPRIMARYRECORD,
          EDUCATIONADDITIONALINFORMATION.DATEADDED
        from
          dbo.EDUCATIONALHISTORY
        inner join 
          dbo.EDUCATIONALINSTITUTION on EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID = EDUCATIONALINSTITUTION.ID
        inner join
          dbo.EDUCATIONALHISTORYSTATUS on EDUCATIONALHISTORY.EDUCATIONALHISTORYSTATUSID = EDUCATIONALHISTORYSTATUS.ID
        left join
          dbo.EDUCATIONALDEGREECODE on EDUCATIONALHISTORY.EDUCATIONALDEGREECODEID = EDUCATIONALDEGREECODE.ID
        left join
          dbo.EDUCATIONALPROGRAMCODE on EDUCATIONALHISTORY.EDUCATIONALPROGRAMCODEID = EDUCATIONALPROGRAMCODE.ID
        left join
          dbo.EDUCATIONALAWARDCODE on EDUCATIONALHISTORY.EDUCATIONALAWARDCODEID = EDUCATIONALAWARDCODE.ID
        left join
          dbo.EDUCATIONADDITIONALINFORMATION on EDUCATIONALHISTORY.ID = EDUCATIONADDITIONALINFORMATION.EDUCATIONALHISTORYID
        left join
    dbo.EDUCATIONALCOLLEGECODE on EDUCATIONADDITIONALINFORMATION.EDUCATIONALCOLLEGECODEID = EDUCATIONALCOLLEGECODE.ID
        left join
          dbo.EDUCATIONALDIVISIONCODE on EDUCATIONADDITIONALINFORMATION.EDUCATIONALDIVISIONCODEID = EDUCATIONALDIVISIONCODE.ID
        left join
          dbo.EDUCATIONALDEPARTMENTCODE on EDUCATIONADDITIONALINFORMATION.EDUCATIONALDEPARTMENTCODEID = EDUCATIONALDEPARTMENTCODE.ID
        left join
          dbo.EDUCATIONALSUBDEPARTMENTCODE on EDUCATIONADDITIONALINFORMATION.EDUCATIONALSUBDEPARTMENTCODEID = EDUCATIONALSUBDEPARTMENTCODE.ID
        left join
          dbo.EDUCATIONALDEGREETYPECODE on EDUCATIONADDITIONALINFORMATION.EDUCATIONALDEGREETYPECODEID = EDUCATIONALDEGREETYPECODE.ID
        where 
          EDUCATIONALHISTORY.ID = @ID;

        select top 1 
          @EDUCATIONALINSTITUTION = EDUCATIONALINSTITUTION, 
          @STATUS = STATUS,
          @PROGRAM = PROGRAM,
          @DEGREE = DEGREE,
          @HONORAWARDED = HONORAWARDED,
          @COMMENTS = COMMENTS,
          @STARTDATE = STARTDATE,
          @ENDDATE = ENDDATE,
          @GRADUATIONDATE = GRADUATIONDATE,
          @CLASSOF = CLASSOF,
          @PREFERREDCLASSOF = PREFERREDCLASSOF,
          @COLLEGE = COLLEGE,
          @DIVISION = DIVISION,
          @DEPARTMENT = DEPARTMENT,
          @SUBDEPARTMENT = SUBDEPARTMENT,
          @DEGREETYPE = DEGREETYPE,
          @ISPRIMARY = ISPRIMARY,
          @DATALOADED = 1
        from @T
        order by
          DATEADDED;

        return 0;