Franchies Search Dynamic SQL Stored Procedure

-- =============================================                                                      
-- Author:  Javed khan                                                      
-- Create date: 25/12/2013                                                    
-- Description: for dynamic search of frnchies  
--Alter date: 01-03-2014
--Alter by : Me                                                    
-- =============================================  
CREATE procedure [dbo].[lsp_busSearchFrnchAdv]    
(    
 @UID int,    
 @ctgryid int,    
 @subctgryid int,    
 @subsubctgryid searchSubsubctgry readonly,    
 @mininvest int,    
 @curncyid int      
)    
as    
begin    
create table #t  
(  
 subsub int  
)  
insert into #t (subsub) select Tsubsubctgryid from @subsubctgryid  
declare @qrystr varchar(max)    
   
set @qrystr='select left(cmp.compname,26) as compname,left(cmp.compaddrs,26) as compaddrs,                    
''UserFiles/100/''+convert(varchar,cmp.usrid)+''/CompDealsOffer/''+convert(varchar,cmp.compid)+''.''+right(cmp.imgurl,3)+'''' as compImg,                    
left(isnull(frn.frnchmngr,''not available''),20) as frnchmngr,frn.frnchid    
,frn.mininvest,cntry1.crncyName as mincurncy,frn.maxinvest,cntry2.crncyName as maxcrncy                  
,frn.minarea,unt.UnitName,frn.commission,frn.commissionin,frn.frnchfee,cntry3.crncyName as feecrncy,                  
frn.frnchterm,frn.TandC,      
vw.name,vw.cityname,vw.image as usrimg,frn.usrid    
from tblfranchisee frn      
inner join tblbusofferComp cmp on frn.cmpnyid=cmp.compid      
inner join (select distinct compid,compctgryid,compsubctgryid,compsubsubctgryid from tblbusoffercompDtl) cmpdtl on frn.cmpnyid= cmpdtl.compid    
left join tblcurrency cntry1 on frn.mincurncy=cntry1.crncyid                
left join tblcurrency cntry2 on frn.maxcurncy=cntry2.crncyid              
               
left join tblcurrency cntry3 on frn.feecurncy=cntry3.crncyid            
left join tblUsrUnitStatic unt on frn.areaunit=unt.UnitId      
inner join vwfrndlistfld vw on frn.usrid=vw.usrid where frn.isdel=0'    
if(@ctgryid!='')    
set @qrystr=@qrystr+' and cmpdtl.compctgryid='+cast(@ctgryid as varchar(150))    
     
if(@subctgryid!='')    
set @qrystr=@qrystr+' and cmpdtl.compsubctgryid='+cast(@subctgryid as varchar(150))    
if((select count(*) from #t)>0)    
set @qrystr=@qrystr+' and cmpdtl.compsubsubctgryid in (select subsub from #t)'    
if(@mininvest!='')    
set @qrystr=@qrystr+' and frn.mininvest>='+cast(@mininvest as varchar(200))    
if(@curncyid!='')    
set @qrystr=@qrystr+' and frn.mincurncy='+cast(@curncyid as varchar(200))    
   
set @qrystr=@qrystr+' order by frn.cdate desc'    
   
exec (@qrystr)    
end