USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTSEARCHDATA

The load procedure used by the view dataform template "RE7 Integration Constituent Search 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.
@FIRSTNAME	nvarchar(50)	INOUT	First name
@MIDDLENAME	nvarchar(50)	INOUT	Middle name
@LASTNAME	nvarchar(100)	INOUT	Last name
@ADDRESSBLOCK	nvarchar(150)	INOUT	Address block
@CITY	nvarchar(50)	INOUT	City
@STATE	nvarchar(5)	INOUT	State
@POSTCODE	nvarchar(12)	INOUT	Postcode
@NICKNAME	nvarchar(50)	INOUT	Nick name
@GENDER	nvarchar(7)	INOUT	Gender
@TITLE1	nvarchar(50)	INOUT	Title 1
@TITLE2	nvarchar(50)	INOUT	Title 2
@SUFFIX1	nvarchar(50)	INOUT	Suffix 1
@SUFFIX2	nvarchar(50)	INOUT	Suffix 2
@STATEDESCRIPTION	nvarchar(100)	INOUT
@COUNTRY	nvarchar(100)	INOUT
@COUNTRYABBREVIATION	nvarchar(5)	INOUT
@NAME	nvarchar(154)	INOUT

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTSEARCHDATA (
                    @ID           uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @FIRSTNAME    nvarchar(50)  = null output,
                    @MIDDLENAME   nvarchar(50)  = null output,
                    @LASTNAME     nvarchar(100) = null output,               
                  @ADDRESSBLOCK nvarchar(150) = null output,
                    @CITY         nvarchar(50)  = null output,
                    @STATE        nvarchar(5)   = null output,
                    @POSTCODE     nvarchar(12)  = null output,
                    @NICKNAME      nvarchar(50)    = null output,
                    @GENDER          nvarchar(7)    = null output,
                    @TITLE1          nvarchar(50)    = null output,
                    @TITLE2          nvarchar(50)    = null output,
                    @SUFFIX1      nvarchar(50)    = null output,
                    @SUFFIX2      nvarchar(50)    = null output,
          @STATEDESCRIPTION nvarchar(100) = null output,
          @COUNTRY nvarchar(100) = null output,
          @COUNTRYABBREVIATION nvarchar(5) = null output,
          @NAME nvarchar(154) = null output
                ) as

                    set nocount on;           
                    set @DATALOADED = 0;

                     -- Constituent Data

                        select
                            @DATALOADED = 1,
                            @FIRSTNAME = CONSTITUENT.FIRSTNAME,
                            @MIDDLENAME = CONSTITUENT.MIDDLENAME,
                            @LASTNAME = CONSTITUENT.KEYNAME,
                            @ADDRESSBLOCK = ADDRESS.ADDRESSBLOCK,
                            @CITY = ADDRESS.CITY,
                            @STATE = STATE.ABBREVIATION,
              @STATEDESCRIPTION = STATE.DESCRIPTION,
                            @POSTCODE = ADDRESS.POSTCODE,
                            @NICKNAME = CONSTITUENT.NICKNAME,
                            @GENDER = CONSTITUENT.GENDER,
                            @TITLE1 = T1.DESCRIPTION,
                            @TITLE2 = T2.DESCRIPTION,
                            @SUFFIX1 = S1.DESCRIPTION,
                            @SUFFIX2 = S2.DESCRIPTION,
              @COUNTRY = COUNTRY.DESCRIPTION,
              @COUNTRYABBREVIATION = COUNTRY.ABBREVIATION,
              @NAME = CONSTITUENT.NAME
                        from
                            dbo.CONSTITUENT
                        left join
                            dbo.ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1
                        left join
                            dbo.STATE on STATE.ID = ADDRESS.STATEID
                        left join
                            dbo.COUNTRY on COUNTRY.ID = ADDRESS.COUNTRYID
                        left join
                            dbo.TITLECODE T1 on T1.ID = CONSTITUENT.TITLECODEID
                        left join
                            dbo.TITLECODE T2 on T2.ID = CONSTITUENT.TITLE2CODEID
                        left join
                            dbo.SUFFIXCODE S1 on S1.ID = CONSTITUENT.SUFFIXCODEID
                        left join
                            dbo.SUFFIXCODE S2 on S2.ID = CONSTITUENT.SUFFIX2CODEID
                        where
                            CONSTITUENT.ID = @ID

                    return 0;