USP_DATALIST_PDACCOUNTSEGMENTMAP

CREATE procedure dbo.USP_DATALIST_PDACCOUNTSEGMENTMAP(
    @ID uniqueidentifier,
  @SEGMENTNAMEORVALUE nvarchar(200) = null
    )
    as

      set nocount on;
    --sanitize the search values, adding mask chars at beginning and end so it performs a 'contains' style match
      set @SEGMENTNAMEORVALUE = dbo.UFN_TEXTSEARCH_SANITIZE(@SEGMENTNAMEORVALUE,'/',DEFAULT,DEFAULT,DEFAULT) ;

   select * 
    from
          (select
              T1.ID,
              --LONGDESCRIPTION,
              case T1.ISCONSTANTVALUE when 1 then '<Constant value>'  else  LONGDESCRIPTION end as LONGDESCRIPTION,
              isnull(nullif(LONGDESCRIPTIONID,''),T1.ID ) as LONGDESCRIPTIONID,
              PDACCOUNTSEGMENTVALUEID,
              T3.SHORTDESCRIPTION,
              T1.PDACCOUNTSTRUCTUREID,
              ISDEFAULT
          from dbo.PDACCOUNTSEGMENTMAPPING T1
              left join dbo.PDACCOUNTSEGMENTVALUE T3 on T1.PDACCOUNTSEGMENTVALUEID = T3.ID
          where T1.PDACCOUNTSTRUCTUREID = @ID and LONGDESCRIPTION is not null

          union all

          select
              NEWID(),
              V1.LONGDESCRIPTION,
              V1.LONGDESCRIPTIONID,
              null,
              null,
              v1.PDACCOUNTSTRUCTUREID,
              0
          from
              dbo.PDACCOUNTSEGMENTMAPPINGVIEW V1
          where 
              V1.PDACCOUNTSTRUCTUREID = @ID
              and
              V1.LONGDESCRIPTIONID not in (select LONGDESCRIPTIONID from PDACCOUNTSEGMENTMAPPING where PDACCOUNTSTRUCTUREID = @ID)) V1
    where
       (V1.LONGDESCRIPTION like @SEGMENTNAMEORVALUE escape '/' or V1.SHORTDESCRIPTION like @SEGMENTNAMEORVALUE escape '/')
        order by 
            case LONGDESCRIPTION when '<Not Used>' then 0 else 1 end, LONGDESCRIPTION