USP_DATAFORMTEMPLATE_VIEW_RELATIONSHIPTOSTUDENT

The load procedure used by the view dataform template "Student Relationship 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.
@RECIPROCALCONSTITUENTID	uniqueidentifier	INOUT	Reciprocal constituent ID
@RELATIONSHIPCONSTITUENTID	uniqueidentifier	INOUT	Relationship constituent ID
@CONSTITUENTNAME	nvarchar(154)	INOUT	Constituent name
@RECIPROCALCONSTITUENTNAME	nvarchar(154)	INOUT	Related constituent
@RELATIONSHIPTYPECODE	nvarchar(100)	INOUT	Constituent is the
@RECIPROCALTYPECODE	nvarchar(100)	INOUT	Constituent is the
@ISSPOUSE	bit	INOUT	Spouse
@ISCONTACT	bit	INOUT	Contact
@ISPRIMARYCONTACT	bit	INOUT	Primary contact
@CONTACTTYPECODE	nvarchar(100)	INOUT	Contact type
@HASJOBINFO	bit	INOUT	HASJOBINFO
@JOBTITLE	nvarchar(100)	INOUT	Job title
@CAREERLEVELCODE	nvarchar(100)	INOUT	Career level
@JOBCATEGORYCODE	nvarchar(100)	INOUT	Category
@INDUSTRYCODE	nvarchar(100)	INOUT	Industry
@JOBSTARTDATE	date	INOUT	Start date
@JOBENDDATE	date	INOUT	End date
@COMMENTS	nvarchar(max)	INOUT	Comments
@ISSTUDENT	bit	INOUT	ISSTUDENT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_RELATIONSHIPTOSTUDENT
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @RECIPROCALCONSTITUENTID uniqueidentifier = null output,
    @RELATIONSHIPCONSTITUENTID uniqueidentifier = null output,    
    @CONSTITUENTNAME nvarchar(154) = null output,
    @RECIPROCALCONSTITUENTNAME nvarchar(154) = null output,
    @RELATIONSHIPTYPECODE nvarchar(100) = null output,
    @RECIPROCALTYPECODE nvarchar(100) = null output,
    @ISSPOUSE bit = null output,
    @ISCONTACT bit = null output,
    @ISPRIMARYCONTACT bit = null output,
    @CONTACTTYPECODE nvarchar(100) = null output,
    @HASJOBINFO bit = null output,
    @JOBTITLE nvarchar(100) = null output,
    @CAREERLEVELCODE nvarchar(100) = null output,
    @JOBCATEGORYCODE nvarchar(100) = null output,
    @INDUSTRYCODE nvarchar(100) = null output,
    @JOBSTARTDATE date = null output,
    @JOBENDDATE date = null output,
    @COMMENTS nvarchar(max) = null output,
    @ISSTUDENT bit = null output
)
as
    set nocount on;

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

    set @DATALOADED = 0;

    declare @RELATIONSHIPSETID uniqueidentifier;

    select 
        @DATALOADED = 1,
        @RELATIONSHIPSETID = RELATIONSHIP.RELATIONSHIPSETID,
        @RECIPROCALCONSTITUENTID = RELATIONSHIP.RECIPROCALCONSTITUENTID,
        @RELATIONSHIPCONSTITUENTID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID,
        @CONSTITUENTNAME = CONSTIT1.NAME,
        @RECIPROCALCONSTITUENTNAME = CONSTIT2.NAME,
        @RELATIONSHIPTYPECODE = dbo.UFN_RELATIONSHIPTYPECODE_GETDESCRIPTION(RELATIONSHIP.RELATIONSHIPTYPECODEID),
        @RECIPROCALTYPECODE = dbo.UFN_RELATIONSHIPTYPECODE_GETDESCRIPTION(RELATIONSHIP.RECIPROCALTYPECODEID),
        @ISSPOUSE = RELATIONSHIP.ISSPOUSE,
        @ISCONTACT = RELATIONSHIP.ISCONTACT,
        @ISPRIMARYCONTACT = RELATIONSHIP.ISPRIMARYCONTACT,
        @CONTACTTYPECODE = dbo.UFN_CONTACTTYPECODE_GETDESCRIPTION(RELATIONSHIP.CONTACTTYPECODEID),
        @COMMENTS = case when len(RELATIONSHIP.COMMENTS) > 256 then left(RELATIONSHIP.COMMENTS,256) + '...' else RELATIONSHIP.COMMENTS end
    from
        dbo.RELATIONSHIP 
    left join 
        dbo.CONSTITUENT as CONSTIT1 
    on 
        RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTIT1.ID
    left join 
        dbo.CONSTITUENT as CONSTIT2 
    on 
        RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTIT2.ID
    where 
        RELATIONSHIP.ID = @ID; 

    set @HASJOBINFO = 0;

    select top 1
        @HASJOBINFO = 1,
        @JOBTITLE = JOBTITLE,
        @CAREERLEVELCODE = dbo.UFN_CAREERLEVELCODE_GETDESCRIPTION(CAREERLEVELCODEID),
        @JOBCATEGORYCODE = dbo.UFN_JOBCATEGORYCODE_GETDESCRIPTION(JOBCATEGORYCODEID),
        @JOBSTARTDATE = STARTDATE,
        @JOBENDDATE = ENDDATE
    from RELATIONSHIPJOBINFO
    where RELATIONSHIPSETID = @RELATIONSHIPSETID
    order by RELATIONSHIPJOBINFO.STARTDATE desc;

    --This field is only displayed for IND-ORG relations so just get the industry for either.

    select
        @INDUSTRYCODE = dbo.UFN_INDUSTRYCODE_GETDESCRIPTION(INDUSTRYCODEID)
    from dbo.ORGANIZATIONDATA
    where ID in (@RECIPROCALCONSTITUENTID, @RELATIONSHIPCONSTITUENTID);

    return 0;