Sql Query for Paging

---

 declare @PageSize int, @PageNumber int, @EMPName varchar(100)
 SET @PageSize=10
 SET @PageNumber=1
 SET @EMPName=''

 

 SELECT TOP(@PageSize) * FROM          
 (SELECT RN = ROW_NUMBER() OVER (ORDER BY Code  DESC), EMPName,EMPAddress,EMPSalary, TotalRows=Count(*) OVER()           
 FROM TL_Emp  WHERE EMPName like '%' + @EMPName+ '%'  
 )  A WHERE A.RN > ((@PageNumber-1)*@PageSize)   

 

OR

 

;with Users(rn,Code,EMPName,EMPAddress,EMPSalary,TotalRows)as     
(SELECT (ROW_NUMBER()over(order by Code desc))as RN,Code,EMPName,EMPAddress,EMPSalary,
count(Code) over() FROM TL_Emp     
WHERE  EMPName like '%' + @EMPName+ '%'  )    
select * from Users where rn between ((@PageNumber-1)*@PageSize)+1 and (@PageNumber*@PageSize)   

Post Comments

---