Something

declare @p_sPropListCode varchar (28)
set  @p_sPropListCode = 'allbmhou'
select distinct l2.hproperty + ','
  From Listprop2 l2 inner join Property P on l2.hproperty=p.hmy and l2.itype =3  
     inner join Property P2 on P2.hMy = L2.hProplist  
     left outer join   
   (  
    select l2.hproperty prop, l2.hproplist listprop From Listprop2 l2   
         inner join Property P on l2.hproperty=p.hmy and l2.itype =3   
                inner join Property P2 on P2.hMy = L2.hProplist  
    Where l2.hproplist in (select hmy from property where scode=@p_sPropListCode)  
     ) listprop on listprop.listprop=l2.hproplist  
             inner join lockout lk on lk.hprop=p.hmy  
       where p2.scode = @p_sPropListCode