Tender Search Dynamic Sql Proc

-- =============================================                                                        
-- Author:  Javed khan                                                        
-- Create date: 17/04/2014                                                      
-- Description: for dynamic search of frnchies    
                                                   
-- =============================================    
CREATE procedure [dbo].[lsp_busSearchTenderAdv]      
(      
 @UID bigint,      
 @tenderType smallint,
 @countryId varchar(3),
 @stateId int,

 @ctgryid int,      
 @subctgryid int,      
       
 @amount int,      
 @curncyid int        
)      
as      
begin      
   
declare @qrystr varchar(max)      
     
set @qrystr='select      
(select COUNT (tl.usrid) from tblUsrLike tl where tl.shrtypId=27 and tl.usrShrid=frn.Tid and tl.sts=1) as liked,                
(select tls.sts from tblUsrLike tls where tls.shrtypId=27 and tls.usrShrid=frn.Tid and tls.usrid=frn.usrid) as likedsts,                
(select COUNT(td.usrid) from tblUsrDislike td where td.shrtypId=27 and td.usrShrid=frn.Tid and td.sts=1) as disliked,                
(select tds.sts from tblUsrDislike tds where tds.shrtypId=27 and tds.usrShrid=frn.Tid and tds.usrid=frn.usrid) as dislikedsts,            
             
                         
cmp.compid,cmp.compName,cmp.estblst,cmp.compaddrs,cmp.compcontact,                          
''UserFiles/100/''+convert(varchar,cmp.usrid)+''/CompDealsOffer/''+convert(varchar,cmp.compid)+''.''+right(cmp.imgurl,3)+'''' as compImg,                          
[dbo].[fn_Bussubsubcat](cmp.compid)as subsubctgry,                          
dtl.compctgryid,dc.subctgryname,ctg.ctgname,dtl.compctgryid,cmp.compweb,                          
frn.Tid,frn.Ttitle,tp.typename,frn.Tprice,cntry1.crncyName as currency,convert(varchar(20),frn.Opndate,101) as Opendate      
,convert(varchar(20),frn.closedate,101) as closedate,      
cntry.cntryname,frn.cntryid,sts.lName as stateName,frn.stateid      
,(case when frn.Tdoc is null then ''No file'' else frn.Tdoc end) as agrmnt,(case when frn.Tdoc is null then ''no file'' else                          
''UserFiles/100/''+convert(varchar,frn.usrid)+''/TenderDoc/''+convert(varchar,frn.Tid)+''.''+[dbo].[getFileEXT](frn.Tdoc)+''''                        
 end) as TenderFile,      
 frn.TandC,frn.Tdec,
 vw.name,vw.cityname,vw.image as usrimg,frn.usrid      
from tbltender frn        
inner join tbltndrType tp on tp.typeid=frn.Ttype                          
left join tblcurrency cntry1 on frn.curncyid=cntry1.crncyid                        
inner join tblbusofferComp cmp on frn.cmpnyid=cmp.compid        
inner join tblcountry cntry on cntry.cntrycode=frn.cntryid      
inner join  tblAddrsLevel1 sts on sts.id=frn.stateid                      
inner join (select distinct compid, compsubctgryid,compctgryid from tblbusoffercompDtl) dtl on cmp.compid=dtl.compid                          
inner join tblusrprflctgry ctg on dtl.compctgryid=ctg.usrprflcatgryid                            
inner join tblUsrPrflSubCtgry dc on dtl.compsubctgryid=dc.usrprflsubctgryid
inner join vwfrndlistfld vw on frn.usrid=vw.usrid                            
where frn.isDel=0'      
if(@tenderType!='')      
set @qrystr=@qrystr+' and frn.Ttype='+cast(@tenderType as varchar(150))      
       
if(@countryId!='')      
set @qrystr=@qrystr+' and frn.cntryid='+cast(@countryId as varchar(150))      
       
if(@stateId!='')      
set @qrystr=@qrystr+' and frn.stateid='+cast(@stateId as varchar(150))      
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(@amount!='')      
set @qrystr=@qrystr+' and frn.Tprice='+cast(@amount as varchar(150))
if(@curncyid!='')      
set @qrystr=@qrystr+' and frn.curncyid='+cast(@curncyid as varchar(150))      
     
set @qrystr=@qrystr+' order by frn.cdate desc'      
     
exec (@qrystr)      
end