UFN_CONSTITUENT_GETIDSFROMCRITERIA

Gets a list of constituent IDs for constituents who meet the supplied criteria.
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@CONSTITUENTID	uniqueidentifier	IN
@CONSTITUENTLOOKUPID	nvarchar(100)	IN
@CONSTITUENTKEYNAME	nvarchar(50)	IN
@CONSTITUENTFIRSTNAME	nvarchar(100)	IN
@MAXROWS	smallint	IN
Definition

Copy


CREATE function dbo.UFN_CONSTITUENT_GETIDSFROMCRITERIA(
    @CONSTITUENTID as uniqueidentifier,
    @CONSTITUENTLOOKUPID as nvarchar(100),
    @CONSTITUENTKEYNAME as nvarchar(50),
    @CONSTITUENTFIRSTNAME as nvarchar(100),
    @MAXROWS smallint = 500
)
returns @MATCHINGIDS table (ID uniqueidentifier)
as
begin

    if @CONSTITUENTID is not null begin
        insert into @MATCHINGIDS (ID) values (@CONSTITUENTID)
    end
    else if @CONSTITUENTFIRSTNAME is not null and @CONSTITUENTFIRSTNAME <> '' and
            @CONSTITUENTKEYNAME is not null and @CONSTITUENTKEYNAME <> '' and 
            @CONSTITUENTLOOKUPID is not null and @CONSTITUENTLOOKUPID <> '' begin
        with LOOKUPIDS_CTE as (
            select top(@MAXROWS)
                C.ID ID
            from
                dbo.CONSTITUENT C
            where
                LOOKUPID = @CONSTITUENTLOOKUPID and C.KEYNAME = @CONSTITUENTKEYNAME and 
                C.FIRSTNAME = @CONSTITUENTFIRSTNAME and ISORGANIZATION = 0 and ISGROUP = 0
            union all
            select top(@MAXROWS)
                C.ID
            from
                dbo.ALTERNATELOOKUPID AL
            inner join
                dbo.CONSTITUENT C on C.ID = AL.CONSTITUENTID and C.KEYNAME = @CONSTITUENTKEYNAME and 
                    C.FIRSTNAME = @CONSTITUENTFIRSTNAME and C.ISORGANIZATION = 0 and C.ISGROUP = 0
            where
                AL.ALTERNATELOOKUPID = @CONSTITUENTLOOKUPID
        )
        insert into @MATCHINGIDS
        select distinct
            ID
        from
            LOOKUPIDS_CTE
    end
    else if @CONSTITUENTKEYNAME is not null and @CONSTITUENTKEYNAME <> '' and
            @CONSTITUENTLOOKUPID is not null and @CONSTITUENTLOOKUPID <> '' begin
        with LOOKUPIDS_CTE as (
            select top(@MAXROWS)
                C.ID ID
            from
                dbo.CONSTITUENT C
            where
                LOOKUPID = @CONSTITUENTLOOKUPID and C.KEYNAME = @CONSTITUENTKEYNAME and 
                ISORGANIZATION = 0 and ISGROUP = 0
            union all
            select top(@MAXROWS)
                C.ID
            from
                dbo.ALTERNATELOOKUPID AL
            inner join
                dbo.CONSTITUENT C on C.ID = AL.CONSTITUENTID and C.KEYNAME = @CONSTITUENTKEYNAME and 
                    C.ISORGANIZATION = 0 and C.ISGROUP = 0
            where
                AL.ALTERNATELOOKUPID = @CONSTITUENTLOOKUPID
        )
        insert into @MATCHINGIDS
        select distinct
            ID
        from
            LOOKUPIDS_CTE
    end
    else if @CONSTITUENTLOOKUPID is not null and @CONSTITUENTLOOKUPID <> '' begin
        with LOOKUPIDS_CTE as (
            select top(@MAXROWS)
                C.ID ID
            from
                dbo.CONSTITUENT C
            where
                LOOKUPID = @CONSTITUENTLOOKUPID and 
                ISORGANIZATION = 0 and ISGROUP = 0
            union all
            select top(@MAXROWS)
                C.ID
            from
                dbo.ALTERNATELOOKUPID AL
            inner join
                dbo.CONSTITUENT C on C.ID = AL.CONSTITUENTID and 
                    C.ISORGANIZATION = 0 and C.ISGROUP = 0
            where
                AL.ALTERNATELOOKUPID = @CONSTITUENTLOOKUPID
        )
        insert into @MATCHINGIDS
        select distinct
            ID
        from
            LOOKUPIDS_CTE
    end

    return 
end