USP_DATALIST_RE7INTEGRATION_RESEARCHGROUPMEMBERS_UNLINKED

Returns a list of all members in a research group not linked to a record in The Raiser's Edge.

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@NAME	nvarchar(154)	IN	Name
@CITY	nvarchar(50)	IN	City
@STATEID	uniqueidentifier	IN	State

Definition

Copy



                CREATE procedure dbo.USP_DATALIST_RE7INTEGRATION_RESEARCHGROUPMEMBERS_UNLINKED (
                    @ID uniqueidentifier,
                    @NAME        nvarchar(154) = null,
                    @CITY        nvarchar(50) = null,
                    @STATEID    uniqueidentifier = null
                ) as
                    set nocount on;

                    set @NAME = replace(replace(@NAME, '*', '%'), '?', '_')
                    set @CITY = replace(replace(@CITY, '*', '%'), '?', '_')

                    select
                        RESEARCHGROUPMEMBER.ID,
                        CONSTITUENT.ID CONSTITUENTID,
                        CONSTITUENT.NAME,
                        ADDRESS.ADDRESSBLOCK,
                        ADDRESS.CITY,
                        STATE.DESCRIPTION,
                        ADDRESS.POSTCODE,
                        CONSTITUENT.LOOKUPID
                    from
                        dbo.RESEARCHGROUPMEMBER
                    left join dbo.CONSTITUENT on
                        CONSTITUENT.ID = RESEARCHGROUPMEMBER.CONSTITUENTID
                    left join dbo.ADDRESS on 
                        ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1
                    left join dbo.STATE on
                        STATE.ID = ADDRESS.STATEID
                    left outer join dbo.RE7INTEGRATIONCONSTITUENTMAP on
                        RE7INTEGRATIONCONSTITUENTMAP.ID = RESEARCHGROUPMEMBER.CONSTITUENTID
                    where
                        RESEARCHGROUPMEMBER.RESEARCHGROUPID = @ID and
                        RE7INTEGRATIONCONSTITUENTMAP.RE7RECORDID is null and
                        (@NAME is null or coalesce(CONSTITUENT.NAME,'') like @NAME + '%') and
                        (@CITY is null or coalesce(ADDRESS.CITY,'') like @CITY + '%') and
                        (@STATEID is null or @STATEID = STATE.ID)
                    order by
                        CONSTITUENT.KEYNAME, CONSTITUENT.FIRSTNAME, CONSTITUENT.MIDDLENAME;