USP_DATAFORMTEMPLATE_PROSPECTALERTPROSPECTSTATUS_VIEW

The load procedure used by the view dataform template "Prospect Alert Prospect Status 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.
@CONSTITUENTNAME	nvarchar(100)	INOUT	Constituent name
@ADDRESSBLOCK	nvarchar(150)	INOUT	Address
@PHONENUMBER	nvarchar(100)	INOUT	Phone number
@EMAILADDRESS	UDT_EMAILADDRESS	INOUT	Email address
@SPOUSENAME	nvarchar(100)	INOUT	Spouse
@PROSPECTMANAGER	nvarchar(100)	INOUT	Prospect manager
@PROSPECTSTATUS	nvarchar(150)	INOUT	Prospect status
@COUNTRYABBREVIATION	nvarchar(5)	INOUT
@STATEABBREVIATION	nvarchar(50)	INOUT
@CITY	nvarchar(50)	INOUT
@POSTCODE	nvarchar(12)	INOUT
@FULLADDRESS	nvarchar(300)	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_PROSPECTALERTPROSPECTSTATUS_VIEW
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @CONSTITUENTNAME nvarchar(100) = null output,
    @ADDRESSBLOCK nvarchar(150) = null output,
    @PHONENUMBER nvarchar(100) = null output,
    @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
    @SPOUSENAME nvarchar(100) = null output,
    @PROSPECTMANAGER nvarchar(100) = null output,
    @PROSPECTSTATUS nvarchar(150) = null output,
    @COUNTRYABBREVIATION nvarchar(5) = null output,
    @STATEABBREVIATION nvarchar(50) = null output,
    @CITY nvarchar(50) = null output,
    @POSTCODE nvarchar(12) = null output,
    @FULLADDRESS nvarchar(300) = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    select
        @CONSTITUENTNAME = NF.NAME,
        @ADDRESSBLOCK = ADDRESS.ADDRESSBLOCK,
        @PHONENUMBER = PHONE.NUMBER,
        @EMAILADDRESS = EMAILADDRESS.EMAILADDRESS,
        @SPOUSENAME = NF_SPOUSE.NAME,
        @PROSPECTMANAGER = NF_PROSPECTMANAGER.NAME,
        @PROSPECTSTATUS = dbo.UFN_PROSPECTSTATUSCODE_GETDESCRIPTION(PROSPECT.PROSPECTSTATUSCODEID),
        @COUNTRYABBREVIATION = dbo.UFN_COUNTRY_GETABBREVIATION(ADDRESS.COUNTRYID),
        @STATEABBREVIATION = dbo.UFN_STATE_GETABBREVIATION(ADDRESS.STATEID),
        @CITY = ADDRESS.CITY,
        @POSTCODE = ADDRESS.POSTCODE,
        @FULLADDRESS = dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID),
        @DATALOADED = 1
    from
        dbo.PROSPECT
    left outer join
        dbo.ADDRESS on ADDRESS.CONSTITUENTID = PROSPECT.ID and ADDRESS.ISPRIMARY = 1
    left outer join
        dbo.PHONE on PHONE.CONSTITUENTID = PROSPECT.ID and PHONE.ISPRIMARY = 1
    left outer join
        dbo.EMAILADDRESS on EMAILADDRESS.CONSTITUENTID = PROSPECT.ID and EMAILADDRESS.ISPRIMARY = 1
    left outer join
        dbo.RELATIONSHIP on RELATIONSHIP.RELATIONSHIPCONSTITUENTID = PROSPECT.ID and RELATIONSHIP.ISSPOUSE = 1
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECT.ID) NF
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RELATIONSHIP.RECIPROCALCONSTITUENTID) NF_SPOUSE
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECT.PROSPECTMANAGERFUNDRAISERID) NF_PROSPECTMANAGER
    where
        PROSPECT.ID = @ID;

    return 0;