Pagination in stored procedure sql server

I have implemented pagination for stored procedure which displays 2 table records one after the other. The pagination logic works perfectly for the first table records but the second table is not displaying records accordingly.

我已经为存储过程实现了分页,它一个接一个地显示2个表记录。分页逻辑适用于第一个表记录,但第二个表没有相应地显示记录。

This is my stored procedure. Where am I going wrong?

这是我的存储过程。我哪里错了?

CREATE PROCEDURE sp_PagedItems
(@Page int,
 @RecsPerPage int)
AS
   SET NOCOUNT ON

   CREATE TABLE #TempItems
   (
        ID int IDENTITY,
        Name varchar(50),
        Price int
   )  

   INSERT INTO #TempItems (Name, Price)
      SELECT 
         Name, Price 
      FROM tblItems 
      ORDER BY Price  

   CREATE TABLE #TempItems1
   (
        ID int IDENTITY,
        Name varchar(50),
        Price int
   ) 

   INSERT INTO #TempItems1 (Name, Price)
      SELECT Name, Price  
      FROM tblItems1 
      ORDER BY Price 

   DECLARE @FirstRec int, @LastRec int

   SELECT @FirstRec = (@Page - 1) * @RecsPerPage
   SELECT @LastRec = (@Page * @RecsPerPage + 1)

   SELECT *
   FROM #TempItems 
   WHERE ID > @FirstRec AND ID < @LastRec 

   SELECT *
   FROM #TempItems1 
   WHERE ID > @FirstRec AND ID < @LastRec 

   -- Turn NOCOUNT back OFF
   SET NOCOUNT OFF

Executing it:

Exec sp_PagedItems 1, 10

The first table displays 10 records in page 1 where as the second table displays only 7 records.

第一个表在第1页中显示10条记录,而第二个表只显示7条记录。

3 个解决方案

#1

If you're using SQL Server 2012 or above, try using OFFSET FETCH statement

如果您使用的是SQL Server 2012或更高版本,请尝试使用OFFSET FETCH语句

https://technet.microsoft.com/en-us/library/gg699618%28v=sql.110%29.aspx

#2

Paginating data from a table using SQL will almost always run into trouble using system generated IDs.

使用SQL从表中分页数据几乎总是会使用系统生成的ID遇到麻烦。

How you actually do pagination will depend on what SQl the RDBMS supports but a general method that will work anywhere is as follows:

你如何实际分页将取决于RDBMS支持的SQl,但是可以在任何地方工作的一般方法如下:

Do your normal select with ordering etc in place

做正常的选择和订购等

cycle through records from the start, counting as you go

从头开始循环记录,随时计算

when you get to (pageCount * recordsPerPage)+1, start collecting the records

当你到达(pageCount * recordsPerPage)+1时,开始收集记录

when you get to (pageCount+1) * recordsPerPage, stop collecting records

当你到达(pageCount + 1)* recordsPerPage时,停止收集记录

It sounds horribly inefficient but is not usually as bad as it looks because if you allow sorting then the records the user is after are usually near the front.

这听起来非常低效,但通常不像它看起来那么糟糕,因为如果你允许排序那么用户所追求的记录通常就在前面。

#3

Yes, this will may be work or not, the reason is same id or ids exist in multiple entry.

是的,这可能是有效的,原因是多个条目中存在相同的id或id。

To resolve, after insert into your temporary table, in select you must use ROW_NUMBER() OVER (order by id ) AS rowID, what I did as :

要解决,插入到临时表后,在select中你必须使用ROW_NUMBER()OVER(按id排序)AS rowID,我做的是:

--set end page no. from calculation of pageno * pagesize
 SELECT   
   @start = CASE WHEN @pageindex > 1   
          THEN   
      ((@PageIndex-1)*@PageSize)+1    
       ELSE   
      @PageIndex   
     END   

 SET @end = @PageIndex*@PageSize  
-- use cte 
;WITH CTE AS    
(    
 your select query 
  --suppose activityID is a column in select query , we used for row_number
)    
--now use cte with rownumber filter
select * from  
(  
 SELECT ROW_NUMBER() OVER (ORDER BY activityID DESC) as rowID,    
 *, noofRows= (SELECT count(activityID) FROM CTE)    
 FROM CTE   
 where CTE.activityTargetID is not null  
) cte  
WHERE    
 rowID between @start AND @end   

ORDER BY CTE.activityID DESC

#1