Pagination in stored procedure sql server

Question

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.

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.

do you have 10 records in the second table? or are there 10 ID's between #FirstRec and @LAstRec — Astra Bear, Feb 20 '15 at 06:44
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Feb 20 '15 at 06:48
@Aswini, ok you have 1000+ records but are there 10 records between the params FirstRec and LastRec — Astra Bear, Feb 20 '15 at 06:50
Naming your tables #TempItems and #TempItems1 sounds like error waiting to happen. Why aren't they called 1 and 2 at least if you don't really figure out any better names? — James Z, Feb 20 '15 at 06:54
I have a different naming for the tables . Here I have changed the names. I am using better names. so it should not be a problem. — Aswini, Feb 20 '15 at 06:59
@AstraBear, I have 10 records bw FirstRec and LastRec in the 1st table bt not in the second table when it is displayed. — Aswini, Feb 20 '15 at 07:02
Did you check this post: http://stackoverflow.com/questions/1744802/how-do-i-write-linqs-skip1000-take100-in-pure-sql where pagination is explained ? — Ako, Feb 20 '15 at 07:05
@Aswini, that seems to be the nub of your problem. You do not have 10 contiguous ID's between FirstRec and LastRec. The ID's have probably been allocated somewhere else. Unless you are actually creating the ID's you cannot do your pagination this way. — Astra Bear, Feb 20 '15 at 07:07

score 3 · Answer 1 · answered Feb 20 '15 at 07:43

3

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

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

answered Feb 20 '15 at 07:43

Evaldas Buinauskas

13,739
11
55
107

score 1 · Answer 2 · answered Feb 20 '15 at 07:23

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

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 :

--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

Astra Bear · Answer 3 · 2015-02-20T07:18:13.437

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

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

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
when you get to (pageCount+1) * recordsPerPage, stop collecting records

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.

Pagination in stored procedure sql server

3 Answers3