USP_SEARCHLIST_USERDEFINEDELEMENT

CREATE procedure dbo.USP_SEARCHLIST_USERDEFINEDELEMENT
(
    @ACCOUNTSTRUCTUREID uniqueidentifier = null,
    @SHORTID nvarchar(100) = null,
    @DESCRIPTION nvarchar(60) = null,
    @INCLUDEINACTIVE bit = 0,
    @MAXROWS smallint = 500,
  @PDACCOUNTSYSTEMID uniqueidentifier = null
)
as
    set nocount on;

    set @SHORTID = coalesce(replace(@SHORTID,'*','%'),'') + '%' ;
    set @DESCRIPTION = coalesce(replace(@DESCRIPTION,'*','%'),'') + '%';

  if @PDACCOUNTSYSTEMID is null
    set @PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B'

    select top(@MAXROWS)
        PDACCOUNTSEGMENTVALUE.ID,
        PDACCOUNTSTRUCTURE.DESCRIPTION as NAME,
        PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION,
        PDACCOUNTSEGMENTVALUE.DESCRIPTION,
    case PREVENTDATAENTRY when 0 then 'No' else 'Yes' + 
      case when PREVENTDATAENTRYBEFOREDATE is null then 
        case when PREVENTDATAENTRYAFTERDATE is null then ''
        else ': After ' + convert(nvarchar(10), PREVENTDATAENTRYAFTERDATE, 101)
        end
      else ': Before ' + convert(nvarchar(10), PREVENTDATAENTRYBEFOREDATE, 101)
        + coalesce(', After ' + convert(nvarchar(10), PREVENTDATAENTRYAFTERDATE, 101), '')
      end
    end as PREVENTDATAENTRY,
    case PREVENTPOST when 0 then 'No' else 'Yes' +
      case when PREVENTPOSTBEFOREDATE is null then 
        case when PREVENTPOSTAFTERDATE is null then ''
        else ': After ' + convert(nvarchar(10), PREVENTPOSTAFTERDATE, 101)
        end
      else ': Before ' + convert(nvarchar(10), PREVENTPOSTBEFOREDATE, 101)
        + coalesce(', After ' + convert(nvarchar(10), PREVENTPOSTAFTERDATE, 101), '')
      end
    end as PREVENTPOSTING
    from 
        dbo.PDACCOUNTSEGMENTVALUE 
    inner join dbo.USERDEFINEDELEMENT on USERDEFINEDELEMENT.ID = PDACCOUNTSEGMENTVALUE.ID
    inner join dbo.PDACCOUNTSTRUCTURE on PDACCOUNTSTRUCTURE.ID = PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID
    where
         (@SHORTID is null or (PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION like @SHORTID)) and
         (@DESCRIPTION is null or (PDACCOUNTSEGMENTVALUE.DESCRIPTION like @DESCRIPTION)) and
         (@INCLUDEINACTIVE = 1 or PDACCOUNTSEGMENTVALUE.ISACTIVE = 1) and
         (@ACCOUNTSTRUCTUREID is null or (PDACCOUNTSTRUCTURE.ID = @ACCOUNTSTRUCTUREID )) and
     PDACCOUNTSTRUCTURE.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and PDACCOUNTSTRUCTURE.ISBASICGL = 0
    order by 
        PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION asc