USP_DATALIST_PDCOMPOSITESEGMENTMAPPING

CREATE procedure dbo.USP_DATALIST_PDCOMPOSITESEGMENTMAPPING(
    @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 T1.ID,
        T1.NAME,
        (select  longdescription from PDCOMPOSITESEGMENTMAPPINGVIEW as a 
        join PDCOMPOSITESEGMENTMAPPINGENTRY as b on a.LONGDESCRIPTIONID = b.LONGDESCRIPTIONID 
        join PDCOMPOSITESEGMENTTABLELIST as c  on b.PDCOMPOSITESEGMENTTABLELISTID = c.id and c.PDACCOUNTTABLESAVAILABLEFORSEGMENTID    = a.PDACCOUNTTABLESAVAILABLEFORSEGMENTID
        where c.SEQUENCE = 1 and b.PDCOMPOSITESEGMENTMAPPINGID = T1.ID) TABLE1, 
        (select longdescription from PDCOMPOSITESEGMENTMAPPINGVIEW as a 
        join PDCOMPOSITESEGMENTMAPPINGENTRY as b on a.LONGDESCRIPTIONID = b.LONGDESCRIPTIONID  
        join PDCOMPOSITESEGMENTTABLELIST as c  on b.PDCOMPOSITESEGMENTTABLELISTID = c.id and c.PDACCOUNTTABLESAVAILABLEFORSEGMENTID    = a.PDACCOUNTTABLESAVAILABLEFORSEGMENTID     
        where c.SEQUENCE = 2 and b.PDCOMPOSITESEGMENTMAPPINGID = T1.ID) TABLE2 ,        
        ISNULL((select  longdescription from PDCOMPOSITESEGMENTMAPPINGVIEW as a 
        join PDCOMPOSITESEGMENTMAPPINGENTRY as b on a.LONGDESCRIPTIONID = b.LONGDESCRIPTIONID 
        join PDCOMPOSITESEGMENTTABLELIST as c  on b.PDCOMPOSITESEGMENTTABLELISTID = c.id and c.PDACCOUNTTABLESAVAILABLEFORSEGMENTID    = a.PDACCOUNTTABLESAVAILABLEFORSEGMENTID    
        where c.SEQUENCE = 3 and b.PDCOMPOSITESEGMENTMAPPINGID = T1.ID),'<Not Used>') TABLE3,         
        ISNULL((select   longdescription from PDCOMPOSITESEGMENTMAPPINGVIEW as a 
        join PDCOMPOSITESEGMENTMAPPINGENTRY as b on a.LONGDESCRIPTIONID = b.LONGDESCRIPTIONID 
        join PDCOMPOSITESEGMENTTABLELIST as c  on b.PDCOMPOSITESEGMENTTABLELISTID = c.id and c.PDACCOUNTTABLESAVAILABLEFORSEGMENTID    = a.PDACCOUNTTABLESAVAILABLEFORSEGMENTID    
        where c.SEQUENCE = 4 and b.PDCOMPOSITESEGMENTMAPPINGID = T1.ID),'<Not Used>') TABLE4        
        ,T2.SHORTDESCRIPTION 
        from dbo.PDCOMPOSITESEGMENTMAPPING T1
        LEFT JOIN PDACCOUNTSEGMENTVALUE T2 ON T1.PDACCOUNTSEGMENTVALUEID = T2.ID
        where T1.PDACCOUNTSTRUCTUREID = @ID 
    and (T1.NAME like @SEGMENTNAMEORVALUE escape '/' or T2.SHORTDESCRIPTION like @SEGMENTNAMEORVALUE escape '/')