Search Tender Client

-- =============================================                                                    
-- Author:  Javed khan                                                    
-- Create date: 26/03/2014                                                  
-- Description: For Searching Tender  for client
---==========================================
Alter procedure [dbo].[lsp_busSearchTndrclient] --159,'s'      
(      
 @usrid bigint,        
 @search varchar(100)        
)        
as
begin


select
(select COUNT (tl.usrid) from tblUsrLike tl where tl.shrtypId=27 and tl.usrShrid=tndr.Tid and tl.sts=1) as liked,      
(select tls.sts from tblUsrLike tls where tls.shrtypId=27 and tls.usrShrid=tndr.Tid and tls.usrid=@usrid) as likedsts,      
(select COUNT(td.usrid) from tblUsrDislike td where td.shrtypId=27 and td.usrShrid=tndr.Tid and td.sts=1) as disliked,      
(select tds.sts from tblUsrDislike tds where tds.shrtypId=27 and tds.usrShrid=tndr.Tid and tds.usrid=@usrid) as dislikedsts,


tndr.Ttitle,convert(varchar(20),tndr.CloseDate,105) as closedate
,sts.lName as stateName,
'UserFiles/100/'+convert(varchar,cmp.usrid)+'/CompDealsOffer/'+convert(varchar,cmp.compid)+'.'+right(cmp.imgurl,3)+'' as compImg
from tbltender tndr inner join tblbusofferComp cmp on tndr.cmpnyid=cmp.compid
inner join tblAddrsLevel1 sts on sts.id=tndr.stateid where tndr.usrid=@usrid
and tndr.Ttitle like @search+'%'
or convert(varchar(12),tndr.cdate,103) like  @search+'%'
or  sts.lName like @search+'%'
and tndr.isdel=0
order by tndr.cdate desc

end