USP_DATAFORMTEMPLATE_VIEW_WPBIOGRAPHICAL2

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@FULLNAME	nvarchar(100)	INOUT
@LINE1	nvarchar(4000)	INOUT
@CITY	nvarchar(4000)	INOUT
@STATE	nvarchar(4000)	INOUT
@ZIP	nvarchar(50)	INOUT
@A2LINE1	nvarchar(4000)	INOUT
@A2CITY	nvarchar(4000)	INOUT
@A2STATE	nvarchar(4000)	INOUT
@A2ZIP	nvarchar(50)	INOUT
@GENDER	nvarchar(7)	INOUT
@BIRTHDATE	datetime	INOUT
@SOURCE	nvarchar(100)	INOUT
@MATCHCODE	nvarchar(100)	INOUT
@CNOTES	nvarchar(1024)	INOUT
@BIRTHPLACE	nvarchar(120)	INOUT
@DECEASEDDATE	UDT_FUZZYDATE	INOUT
@OCCUPATION	nvarchar(4000)	INOUT
@EDUCATION	nvarchar(max)	INOUT
@FAMILY	nvarchar(max)	INOUT
@POSITIONSHELD	nvarchar(max)	INOUT
@CERTIFICATIONS	nvarchar(max)	INOUT
@CAREER	nvarchar(max)	INOUT
@AWARDS	nvarchar(max)	INOUT
@MEMBERSHIPS	nvarchar(max)	INOUT
@RESEARCHINTERESTS	nvarchar(max)	INOUT
@POLITICALRELIGIOUSAFFILIATIONS	nvarchar(max)	INOUT
@CIVICMILITARYSERVICE	nvarchar(max)	INOUT
@THOUGHTSONLIFE	nvarchar(max)	INOUT
@LAW	nvarchar(max)	INOUT
@PERSONAL	nvarchar(max)	INOUT
@HOMEADDRESS	nvarchar(4000)	INOUT
@BUSINESSADDRESS	nvarchar(4000)	INOUT

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_WPBIOGRAPHICAL2 (
                    @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,
                    @SOURCE nvarchar(100) = null output,
                    @MATCHCODE nvarchar(100) = 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,
                        @SOURCE = SOURCE,
                        @MATCHCODE = MC + ' - ' + dbo.UFN_WPMATCHCODE_DESCRIPTION(MC),
                        @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;