USP_RE7CONSTITUENTSEARCH

Parameters

Parameter	Parameter Type	Mode
@CONSTITUENTID	nvarchar(100)	IN
@KEYNAME	nvarchar(100)	IN
@FIRSTNAME	nvarchar(50)	IN
@EMAILADDRESS	nvarchar(100)	IN
@CITY	nvarchar(50)	IN
@STATE	int	IN
@POSTCODE	nvarchar(12)	IN
@SYSTEMRECORDID	int	IN
@ADDRESSLINES	nvarchar(100)	IN
@CLASSOF	int	IN
@MAXROWS	smallint	IN
@CONSTITUENTONLY	bit	IN
Definition

Copy


      create procedure dbo.USP_RE7CONSTITUENTSEARCH
      (
      @CONSTITUENTID nvarchar(100) = null,
      @KEYNAME nvarchar(100) = null,
      @FIRSTNAME nvarchar(50) = null,
      @EMAILADDRESS nvarchar(100) = null,
      @CITY nvarchar(50) = null,
      @STATE int = null,
      @POSTCODE nvarchar(12) = null,
      @SYSTEMRECORDID int = null,
      @ADDRESSLINES nvarchar(100) = null,
      @CLASSOF int = null,
      @MAXROWS smallint = 0,
      @CONSTITUENTONLY bit = 1
      )
      as
      set nocount on;

      if @CONSTITUENTID is not null
        set @CONSTITUENTID = '%' + isnull(@CONSTITUENTID, '') + '%';

      if @KEYNAME is not null
        set @KEYNAME = '%' + isnull(@KEYNAME, '') + '%';

      if @FIRSTNAME is not null
        set @FIRSTNAME = '%' + isnull(@FIRSTNAME, '') + '%';

      if @EMAILADDRESS is not null
        set @EMAILADDRESS = '%' + isnull(@EMAILADDRESS, '') + '%';

      if @CITY is not null
        set @CITY = '%' + isnull(@CITY, '') + '%';

      if @POSTCODE is not null
        set @POSTCODE = '%' + isnull(@POSTCODE, '') + '%';

      if @ADDRESSLINES is not null
        set @ADDRESSLINES = '%' + isnull(@ADDRESSLINES, '') + '%';

      set @CONSTITUENTONLY = isnull(@CONSTITUENTONLY, 1);

      select distinct top (@MAXROWS)
      [C].[LOCALID] [ID],
      [C].[FULLNAME],
      [C].[LOOKUPID] [CONSTITUENTID],
      [C].[KEYNAME],
      [C].[FIRSTNAME],
      [CD].[EMAILADDRESS],
      [A].[CITY],
      [S].[DESCRIPTION] [STATE],
      [A].[POSTCODE],
      [C].[MIDDLENAME]
      from dbo.[RE7_CONSTITUENT] as [C]
      left outer join dbo.[RE7_CONSTITUENTPREFERRED] as [CD] on [C].[LOCALID] = [CD].[LOCALID]
      left outer join dbo.[RE7_ADDRESS] as [A] on [CD].[PREFERREDADDRESS_LOCALID] = [A].[LOCALID]
      left outer join dbo.[RE7_STATE] as [S] on [A].[STATE_LOCALID] = [S].[LOCALID] 
      left outer join dbo.[RE7_CONSTITUENTEDUCATION] [CE] on [C].[LOCALID] = [CE].[CONSTITUENT_LOCALID]

      where
      (@CONSTITUENTONLY = 0 or ([C].[ISCONSTITUENT] = @CONSTITUENTONLY))
      and
      (@CONSTITUENTID is null or ([C].[LOOKUPID] like @CONSTITUENTID))
      and
      (@KEYNAME is null or ([C].[KEYNAME] like @KEYNAME))
      and
      (@FIRSTNAME is null or ([C].[FIRSTNAME] like @FIRSTNAME))
      and
      (@EMAILADDRESS is null or ([CD].[EMAILADDRESS] like @EMAILADDRESS))
      and
      (@CITY is null or ([A].[CITY] like @CITY))
      and
      (@STATE is null or ([S].[LOCALID] = @STATE))
      and
      (@POSTCODE is null or ([A].[POSTCODE] like @POSTCODE))
      and
      (@SYSTEMRECORDID is null or ([C].[LOCALID] = @SYSTEMRECORDID))
      and
      (@ADDRESSLINES is null or 
        (
           ([A].[ADDRESSLINE1] like @ADDRESSLINES)
        or ([A].[ADDRESSLINE2] like @ADDRESSLINES)
        or ([A].[ADDRESSLINE3] like @ADDRESSLINES)
        or ([A].[ADDRESSLINE4] like @ADDRESSLINES)
        or ([A].[ADDRESSLINE5] like @ADDRESSLINES)
        )
      )
      and
      (@CLASSOF is null or ([CE].[CLASSOF] = @CLASSOF))
      order by [C].[KEYNAME], [C].[FIRSTNAME], [C].[MIDDLENAME];

      return 0;