Using row_number() Sql query

create table #t
(
s int
)

create table #p
(
s int
)

insert into #t(s) values(1)
insert into #t(s) values(2)
insert into #p(s) values(3)
insert into #p(s) values(4)

insert into #t(s) values(3)
insert into #t(s) values(4)
insert into #p(s) values(5)
insert into #p(s) values(6)

  select a.s, b.s from
  (select t1.s, ROW_NUMBER() over(order by t1.s) a from #t t1) a

 join

  (select t2.s, ROW_NUMBER() over(order by t2.s) b from #p t2 ) b
  on a.a=b.b

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 

show visiting card for evisiting card

 =============================================                      

                                                           

-- Create date: 09/04/2013                                      

-- Description: Show visiting card for multiple profile                                      

-- =============================================                                      

CREATE proc [dbo].[lsp_MultPrflVisitingcard]                                              

@usrid bigint                                             

as                                                 

                                      

begin                                  

  select  top 100   

 (select COUNT(*) from tblusrNoOfViews where mdlid=21 and shrid=p.usrprflid) as totalview,        

(select count(tmp.usrid) from       

(select distinct usrid from tblUsrShrNtwrk where shrType=21 and shrid=p.usrprflId      

 union      

select distinct usrid from tblUsrShrGrpNtwrk where shrType=21 and shrid=p.usrprflId) as tmp) as shared,      

(select ReportTypStId from tblUsrReport where  mdlid=21 and ShrId=p.usrprflId and usrid=@usrid) as reportsts,            

(select count(*) from tblusrrating where  mdlid=21 and ShrId=p.usrprflId) as raters,            

(select count(*) from tblusrrating where  mdlid=21 and ShrId=p.usrprflId and usrid=@usrid) as ratests ,             

(select (cast(max(rating)as float))/2  from tblusrrating where  mdlid=21 and ShrId=p.usrprflId and usrid=@usrid) as rated ,             

(select (sum((cast(rating as float))/2))/(select COUNT(usrid) from tblusrrating where  mdlid=21 and ShrId=p.usrprflId) from tblusrrating where  mdlid=21 and ShrId=p.usrprflId) as rating,           

(select COUNT(usrid) from tblUsrShrCmntOn where UsrShrId=p.usrprflId and ShrTypId=21) as commntd,           

(select COUNT (tl.usrid) from tblUsrLike tl where tl.shrtypId=21 and tl.usrShrid=p.usrprflId and tl.sts=1) as liked,           

(select tls.sts from tblUsrLike tls where tls.shrtypId=21 and tls.usrShrid=p.usrprflId and tls.usrid=@usrid) as likedsts,           

(select COUNT(td.usrid) from tblUsrDislike td where td.shrtypId=21 and td.usrShrid=p.usrprflId and td.sts=1) as disliked,          

(select tds.sts from tblUsrDislike tds where tds.shrtypId=21 and tds.usrShrid=p.usrprflId and tds.usrid=@usrid) as dislikedsts,          

          

          

          

   vw.name, ([dbo].[fn_subsubcat](p.usrprflId)) as subsubctgryname, cm.compnyname,p.estb, desg.desg, d.usrprflctgryId,  c.ctgname, p.usrprflid, p.usrid, prfname.usrprfltypeid, prfname.typename,sc.usrprflsubctgryId, sc.subctgryname, ('UserFiles/100/'      

  

+cast(p.usrid as varchar)+'/Evisitingcard/'+cast(prfname.usrprflTypeId as varchar)+'/' + cast(p.usrprflId as varchar)+'.'+RIGHT(p.imgurl, 3)) as imgurl, p.discriptn,p.InspBy,p.Achvmnts,p.Planing,p.Addrs1,p.contct1,p.Email,p.Websit from tblusrprfl p       

  

    

      

                        

          

  inner join (select distinct usrprflctgryId, usrprflSubctgryId , usrprflid from tblusrprfldtl) d on p.usrprflid=d.usrprflid                                 

  left join  tblUsrPrflSubCtgry  sc on sc.usrprflsubctgryId = d.usrprflSubctgryId                                

  left join tblusrprflctgry c on c.usrprflcatgryid=d.usrprflctgryid                    

  left join tblusrcompnystatic cm on cm.usrcompnyId=p.compnyid                   

  left join tblusrPrflDesgStatic desg on desg.usrjobDesgntnStaticId=p.desgid                           

  inner join vwfrndlistfld vw on p.usrId=vw.usrid               

                               

  inner join tblusrprfltypeStatic prfname on prfname.usrprflTypeId= p.usrprfltypeId where p.isdel !=1 and p.usrid=@usrid                            

  order by p.cdate desc                    

end   

Temporary Tables in SQL Server

Read Here