UFN_WEBFORMS_CONSTITUENT_EXACTAUTOMATCH

            CREATE function dbo.UFN_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 = '',
                @ADDRESSSUBSTITUTIONS dbo.UDT_STRINGPAIR_100 readonly
            )
            -- 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)

            returns @CONSTITUENTS table (
                [ID] uniqueidentifier,
                [KEYNAME] nvarchar(100),
                [FIRSTNAME] nvarchar(50)
            )
            as 
            begin
                --Known Web Forms Auto match Parameters

                if len(@POSTCODE) > 0 
                    select @POSTCODE = left(@POSTCODE, 5)+'%';

                select @PHONENUMBER = dbo.UFN_PHONE_REMOVEFORMATTING(@PHONENUMBER)

                --If the user provided this data, we should check the constituent record's

                declare
                    @OPTIONALLYCHECKEMAIL bit = 0,
                    @OPTIONALLYCHECKPHONE bit = 0,
                    @OPTIONALLYCHECKTITLE bit = 0,
                    @OPTIONALLYCHECKFIRSTNAME bit = 0;

                select
                    @OPTIONALLYCHECKEMAIL = case when len(@EMAILADDRESS) > 0 then 1 else 0 end,
                    @OPTIONALLYCHECKPHONE = case when len(@PHONENUMBER) > 0 then 1 else 0 end,
                    @OPTIONALLYCHECKTITLE = case when @TITLECODEID is not null then 1 else 0 end,
                    @OPTIONALLYCHECKFIRSTNAME = case when len(@FIRSTNAME) > 0 then 1 else 0 end;

                --Scrub for case sensitivity ("Robert" and "robert" should be an exact match)

                select
                    @EMAILADDRESS = lower(@EMAILADDRESS),
                    @KEYNAME = lower(isnull(@KEYNAME,'')),
                    @FIRSTNAME = lower(@FIRSTNAME),
                    @ADDRESSBLOCK = dbo.UFN_ADDRESS_ADDRESSBLOCK_NORMALIZE_LOWERCASE(@ADDRESSBLOCK, @ADDRESSSUBSTITUTIONS),
                    @CITY = lower(isnull(@CITY,''));

                --Leave work early if required fields aren't provided

                if    len(@KEYNAME) = 0 or
                    len(@ADDRESSBLOCK) = 0 or
                    len(@CITY) = 0 or
                    @COUNTRYID = null
                        return;

                with [CTE_ALIAS] as (
                    select [ALIAS].[CONSTITUENTID]
                    from dbo.[ALIAS]
                    where 
                        lower([KEYNAME]) = @KEYNAME and
                        (
                            @OPTIONALLYCHECKFIRSTNAME = 0 or
                            lower(FIRSTNAME) = @FIRSTNAME or
                            FIRSTNAME = ''
                        ) and
                        (
                            @OPTIONALLYCHECKTITLE = 0 or
                            ([ALIAS].[TITLECODEID] = @TITLECODEID or [ALIAS].[TITLE2CODEID] = @TITLECODEID) or
                            ([ALIAS].[TITLECODEID] is null and [ALIAS].[TITLE2CODEID] is null)
                        )
                ),
                [CTE_CONSTITUENT] as (
                    select
                        [CONSTITUENT].[ID],
                        [CONSTITUENT].[KEYNAME],
                        [CONSTITUENT].[FIRSTNAME],
                        [CONSTITUENT].[TITLECODEID],
                        [CONSTITUENT].[TITLE2CODEID],
                        [CONSTITUENT].[NICKNAME]
                    from dbo.[CONSTITUENT] with (nolock)
                    left join [CTE_ALIAS] as [ALIAS]
                        on [CONSTITUENT].[ID] = [ALIAS].[CONSTITUENTID]
                    left join [dbo].[DECEASEDCONSTITUENT] with (nolock)
                        on CONSTITUENT.ID = DECEASEDCONSTITUENT.ID
                    where
                        (
                            [ALIAS].[CONSTITUENTID] is not null or
                            (
                                lower([CONSTITUENT].[KEYNAME]) = @KEYNAME and
                                (
                                    @OPTIONALLYCHECKFIRSTNAME = 0 or
                                    lower([CONSTITUENT].[FIRSTNAME]) = @FIRSTNAME or
                                    [CONSTITUENT].[FIRSTNAME] = '' or
                                    lower([CONSTITUENT].[NICKNAME]) = @FIRSTNAME
                                ) and
                                (
                                    @OPTIONALLYCHECKTITLE = 0 or
                                    ([CONSTITUENT].[TITLECODEID] = @TITLECODEID or [CONSTITUENT].[TITLE2CODEID] = @TITLECODEID) or
                                    ([CONSTITUENT].[TITLECODEID] is null and [CONSTITUENT].[TITLE2CODEID] is null)
                                )
                            )
                        ) and
                        [CONSTITUENT].[ISINACTIVE] = 0 and 
                        [DECEASEDCONSTITUENT].[ID] is null and 
                        ([CONSTITUENT].[ISGROUP] = 0 and [CONSTITUENT].[ISORGANIZATION] = 0)
                ),
                [CTE_CONSTITUENT_FILTERED] as (
                    select
                        [CONSTITUENT].[ID],
                        [CONSTITUENT].[KEYNAME],
                        [CONSTITUENT].[FIRSTNAME],
                         dbo.UFN_ADDRESS_ADDRESSBLOCK_NORMALIZE_LOWERCASE([ADDRESS].[ADDRESSBLOCK], @ADDRESSSUBSTITUTIONS) as [ADDRESSBLOCK]
                    from [CTE_CONSTITUENT] as [CONSTITUENT]
                    inner join dbo.[ADDRESS] with (nolock)
                        on [CONSTITUENT].[ID] = [ADDRESS].[CONSTITUENTID]
                    left join dbo.[PHONE] with (nolock)
                        on [CONSTITUENT].[ID] = [PHONE].[CONSTITUENTID]
                    left join dbo.[EMAILADDRESS] with (nolock)
                        on [CONSTITUENT].[ID] = [EMAILADDRESS].[CONSTITUENTID]
                    where 
                        @COUNTRYID = [ADDRESS].[COUNTRYID] and
                        [ADDRESS].[POSTCODE] like @POSTCODE and
                        @CITY = lower([ADDRESS].[CITY]) and
                        (
                            (@STATEID is null and [ADDRESS].[STATEID] is null) or
                            @STATEID = [ADDRESS].[STATEID]
                        ) and
                        (
                            @OPTIONALLYCHECKEMAIL = 0 or
                            [EMAILADDRESS].[ID] is null or
                            [EMAILADDRESS].[EMAILADDRESS] = '' or
                            @EMAILADDRESS = lower([EMAILADDRESS].[EMAILADDRESS])
                        ) and
                        (
                            @OPTIONALLYCHECKPHONE = 0 or
                            [PHONE].[ID] is null or
                            @PHONENUMBER = [PHONE].[NUMBERNOFORMAT] 
                        )
                )

                insert into @CONSTITUENTS
                select distinct top(@MAXROWS)
                    [CONSTITUENT].[ID],
                    [CONSTITUENT].[KEYNAME],
                    [CONSTITUENT].[FIRSTNAME]
                from [CTE_CONSTITUENT_FILTERED] as [CONSTITUENT]
                where @ADDRESSBLOCK = [CONSTITUENT].[ADDRESSBLOCK]

                return
            end