USP_RE7MEMBERSHIPRENEWALSEARCH

      create procedure dbo.USP_RE7MEMBERSHIPRENEWALSEARCH
      (
      @NAMEONRENEWAL nvarchar(100) = null,
      @SENTTOCODE nvarchar(1) = null,
      @MAXROWS smallint = 0
      )
      as
      set nocount on;

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

      select  top (@MAXROWS)
        [MR].[LOCALID] [ID],
        [MR].[NAMEONRENEWAL],
        case [MR].[SENTTOCODE_LOCALID] when 0 then 'Donor' when 1 then 'Primary member' when 2 then 'Both' end [SENTTO],
        [C].[FULLNAME],
        case when isnull([MR].[NAMEONRENEWAL], '') = '' then [C].[FULLNAME] + ' - ' + case [MR].[SENTTOCODE_LOCALID] when 0 then 'Donor' when 1 then 'Primary member' when 2 then 'Both' end
        else [C].[FULLNAME] + ' - ' + case [MR].[SENTTOCODE_LOCALID] when 0 then 'Donor' when 1 then 'Primary member' when 2 then 'Both' end + ' - ' + [MR].[NAMEONRENEWAL] end [IDENTIFIER]
      from [dbo].[RE7_MEMBERSHIPRENEWAL] [MR]
      left outer join dbo.[RE7_CONSTITUENTMEMBERSHIP] [CM] on [MR].[TRANSACTION_LOCALID] = [CM].[LOCALID]
      left outer join dbo.[RE7_CONSTITUENT] [C] on [CM].[CONSTITUENT_LOCALID] = [C].[LOCALID]
      where
        (@NAMEONRENEWAL is null or ([MR].[NAMEONRENEWAL] like @NAMEONRENEWAL))
      and
        (@SENTTOCODE is null or ([MR].[SENTTOCODE_LOCALID] = cast(@SENTTOCODE as int)));

      return 0;