USP_DATAFORMTEMPLATE_VIEW_WPBIOGRAPHICAL

The load procedure used by the view dataform template "WealthPoint Biographical 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.
@FULLNAME	nvarchar(100)	INOUT	Name
@LINE1	nvarchar(4000)	INOUT	Address
@CITY	nvarchar(4000)	INOUT	City
@STATE	nvarchar(4000)	INOUT	State
@ZIP	nvarchar(50)	INOUT	ZIP
@A2LINE1	nvarchar(4000)	INOUT	Address
@A2CITY	nvarchar(4000)	INOUT	City
@A2STATE	nvarchar(4000)	INOUT	State
@A2ZIP	nvarchar(50)	INOUT	ZIP
@GENDER	nvarchar(7)	INOUT	Gender
@BIRTHDATE	datetime	INOUT	Birth date
@CNOTES	nvarchar(1024)	INOUT	Notes
@BIRTHPLACE	nvarchar(120)	INOUT	Birthplace
@DECEASEDDATE	UDT_FUZZYDATE	INOUT	Deceased date
@OCCUPATION	nvarchar(4000)	INOUT	Occupation
@EDUCATION	nvarchar(max)	INOUT	Education
@FAMILY	nvarchar(max)	INOUT	Family
@POSITIONSHELD	nvarchar(max)	INOUT	Positions held
@CERTIFICATIONS	nvarchar(max)	INOUT	Certifications
@CAREER	nvarchar(max)	INOUT	Career
@AWARDS	nvarchar(max)	INOUT	Awards
@MEMBERSHIPS	nvarchar(max)	INOUT	Memberships
@RESEARCHINTERESTS	nvarchar(max)	INOUT	Avocations/Research/Interests
@POLITICALRELIGIOUSAFFILIATIONS	nvarchar(max)	INOUT	Political/Religious affiliations
@CIVICMILITARYSERVICE	nvarchar(max)	INOUT	Civic/Military service
@THOUGHTSONLIFE	nvarchar(max)	INOUT	Thoughts on life
@LAW	nvarchar(max)	INOUT	Law
@PERSONAL	nvarchar(max)	INOUT	Personal
@HOMEADDRESS	nvarchar(4000)	INOUT	Home address
@BUSINESSADDRESS	nvarchar(4000)	INOUT	Business address

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_WPBIOGRAPHICAL (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @FULLNAME nvarchar(100) = null output,
                    @LINE1 nvarchar(4000) = null output,
                    @CITY nvarchar(4000) = null output,
                    @STATE nvarchar(4000) = null output,
                    @ZIP nvarchar(50) = null output,
                    @A2LINE1 nvarchar(4000) = null output,
                    @A2CITY nvarchar(4000) = null output,
                    @A2STATE nvarchar(4000) = null output,
                    @A2ZIP nvarchar(50) = null output,
                    @GENDER nvarchar(7) = null output,
                    @BIRTHDATE datetime = null output,
                    @CNOTES nvarchar(1024) = null output,
                    @BIRTHPLACE nvarchar(120) = null output,
                    @DECEASEDDATE dbo.UDT_FUZZYDATE = null output,
                    @OCCUPATION nvarchar(4000) = null output,
                    @EDUCATION nvarchar(max) = null output,
                    @FAMILY nvarchar(max) = null output,
                    @POSITIONSHELD nvarchar(max) = null output,
                    @CERTIFICATIONS nvarchar(max) = null output,
                    @CAREER nvarchar(max) = null output,
                    @AWARDS nvarchar(max) = null output,
                    @MEMBERSHIPS nvarchar(max) = null output,
                    @RESEARCHINTERESTS nvarchar(max) = null output,
                    @POLITICALRELIGIOUSAFFILIATIONS nvarchar(max) = null output,
                    @CIVICMILITARYSERVICE nvarchar(max) = null output,
                    @THOUGHTSONLIFE nvarchar(max) = null output,
                    @LAW nvarchar(max) = null output,
                    @PERSONAL nvarchar(max) = null output,
                    @HOMEADDRESS nvarchar(4000) = null output,
                    @BUSINESSADDRESS nvarchar(4000) = null output
                ) as begin

                    set nocount on;

                    set @DATALOADED = 0;

                    select 
                        @DATALOADED = 1,
                        @FULLNAME = FULLNAME,
                        @LINE1 = LINE1,
                        @CITY = CITY,
                        @STATE = STATE,
                        @ZIP = ZIP,
                        @A2LINE1 = A2LINE1,
                        @A2CITY = A2CITY,
                        @A2STATE = A2STATE,
                        @A2ZIP = A2ZIP,
                        @GENDER = GENDER,
                        @BIRTHDATE = BIRTHDATE,
                        @CNOTES = CNOTES,
                        @BIRTHPLACE = BIRTHPLACE,
                        @DECEASEDDATE = DECEASEDDATE,
                        @OCCUPATION = OCCUPATION,
                        @EDUCATION = EDUCATION,
                        @FAMILY = FAMILY,
                        @POSITIONSHELD = POSITIONSHELD,
                        @CERTIFICATIONS = CERTIFICATIONS,
                        @CAREER = CAREER,
                        @AWARDS = AWARDS,
                        @MEMBERSHIPS = MEMBERSHIPS,
                        @RESEARCHINTERESTS = RESEARCHINTERESTS,
                        @POLITICALRELIGIOUSAFFILIATIONS = POLITICALRELIGIOUSAFFILIATIONS,
                        @CIVICMILITARYSERVICE = CIVICMILITARYSERVICE,
                        @THOUGHTSONLIFE = THOUGHTSONLIFE,
                        @LAW = LAW,
                        @PERSONAL = PERSONAL,
                        @HOMEADDRESS = @LINE1 + 
                            case when len(@LINE1) > 0 and len(@CITY + @STATE + @ZIP) > 0 then char(13) + char(10) else '' end + @CITY +
                            case when len(@CITY) > 0 and len(@STATE) > 0 then ', ' else '' end + @STATE +
                            case when LEN(@CITY + @STATE) > 0 then ' ' else '' end + @ZIP,
                        @BUSINESSADDRESS = @A2LINE1 + 
                            case when len(@A2LINE1) > 0 and len(@A2CITY + @A2STATE + @A2ZIP) > 0 then char(13) + char(10) else '' end + @A2CITY +
                            case when len(@A2CITY) > 0 and len(@A2STATE) > 0 then ', ' else '' end + @A2STATE +
                            case when LEN(@A2CITY + @A2STATE) > 0 then ' ' else '' end + @A2ZIP    
                    from
                        dbo.WPBIOGRAPHICAL
                    where
                        ID=@ID;

                    return 0;

                end;