USP_DATALIST_WEBFORMS_CONSTITUENT_EXACTAUTOMATCH

List of constituents matching web forms criteria exactly.

Parameters

Parameter	Parameter Type	Mode	Description
@MAXROWS	int	IN	Input parameter indicating the maximum number of rows to return.
@KEYNAME	nvarchar(100)	IN	Last Name
@FIRSTNAME	nvarchar(50)	IN	First Name
@TITLECODEID	uniqueidentifier	IN	Title
@ADDRESSBLOCK	nvarchar(150)	IN	Address Block
@CITY	nvarchar(100)	IN	Address City
@COUNTRYID	uniqueidentifier	IN	Country
@STATEID	uniqueidentifier	IN	State
@POSTCODE	nvarchar(12)	IN	Address Zip Code
@PHONENUMBER	nvarchar(100)	IN	Phone Number
@EMAILADDRESS	UDT_EMAILADDRESS	IN	Email Address

Definition

Copy



            CREATE procedure dbo.USP_DATALIST_WEBFORMS_CONSTITUENT_EXACTAUTOMATCH(
                @MAXROWS integer = 2,
                @KEYNAME nvarchar(100) = '',
                @FIRSTNAME nvarchar(50) = '',
                @TITLECODEID uniqueidentifier = null,
                @ADDRESSBLOCK nvarchar(150)    = '',
                @CITY nvarchar(100) = '',
                @COUNTRYID uniqueidentifier= null,
                @STATEID uniqueidentifier= null,
                @POSTCODE nvarchar(12) = '',
                @PHONENUMBER nvarchar(100) = '',
                @EMAILADDRESS dbo.UDT_EMAILADDRESS = ''
            )
            -- Only returns a list of individuals (not groups or organizations)

            -- Including these types will probably mean some consideration for how to handle first name (since these constituent types don't have theses)

            as
                set nocount on;

                --Address substitutions for address block normalization/matching

                declare @ADDRESSSUBSTITUTIONS dbo.UDT_STRINGPAIR_100
                insert into @ADDRESSSUBSTITUTIONS
                select 
                    [DESIGNATION],
                    [ABBREVIATION]
                from dbo.UFN_ADDRESS_ADDRESSBLOCK_STANDARDUSABBREVIATIONS_LOWERCASE()

                select
                    [CONSTITUENT].[ID],
                    [CONSTITUENT].[KEYNAME],
                    [CONSTITUENT].[FIRSTNAME]
                from dbo.UFN_WEBFORMS_CONSTITUENT_EXACTAUTOMATCH (
                    @MAXROWS,
                    @KEYNAME,
                    @FIRSTNAME,
                    @TITLECODEID,
                    @ADDRESSBLOCK,
                    @CITY,
                    @COUNTRYID,
                    @STATEID,
                    @POSTCODE,
                    @PHONENUMBER,
                    @EMAILADDRESS,
                    @ADDRESSSUBSTITUTIONS
                ) [CONSTITUENT]