Server Side Paging With SQL Server 2005

• Davor Geci

  SSC-Addicted

  Points: 487

  More actions

  March 28, 2009 at 5:55 pm

  #967963

  I have played a little bid with this code.

  Here are 2 methods, 1. method that search a record and 2. method that retrives a page.

  1. method

  -- big thanks goes to Jacob Sebastian, creator of this algorithm

  -- regards Davor Geci

  -------------------------------------------------------------------------

  -------------------------------------------------------------------------

  -------------------------------------------------------------------------

  --1. if you want to retrive page on whitch is the record @FindThisRecord

  -------------------------------------------------------------------------

  -------------------------------------------------------------------------

  -------------------------------------------------------------------------

  DECLARE @PageNumber AS BIGINT = 1;

  DECLARE @Pages AS FLOAT = 0;

  DECLARE @RowsPerPage AS FLOAT = 100;

  DECLARE @RecIDNum AS BIGINT = 0;

  DECLARE @Records AS FLOAT = 0;

  DECLARE @FindThisRecord AS FLOAT = 98111220929667;

  --**************************** PART 1 *****************************

  -- order records by Order By

  -- if you have WHERE or JOINS put also here for accurate results

  -- here you need to retrive only RecID and field for searching your record

  WITH tmp1 AS

  (SELECT ROW_NUMBER () OVER (ORDER BY UFAGL.BRRAC DESC ) AS RecID,

  UFAGL.BRRAC

  FROM UFAGL)

  -- get the position of my record and total number of records

  SELECT @RecIDNum = RecId,

  @Records = (select max(RecID) from tmp1)

  FROM tmp1

  WHERE BRRAC = @FindThisRecord -- if you don't have record to find skip this WHERE

  SET @PageNumber = ceiling (@RecIDNum/@RowsPerPage) ; -- get the page with my record, if you don't have record to find skip this

  SET @Pages = ceiling (@Records/@RowsPerPage) ; -- get number of total pages

  --**************************** PART 2 *****************************

  -- this is the acctual sql for getting my records ordered by order by field

  -- if you have WHERE or JOINS put also here for accurate results

  WITH tmp AS

  (SELECT ROW_NUMBER () OVER (ORDER BY UFAGL.BRRAC DESC ) AS RecID ,

  [UFAGL].[STATUS] AS [St] ,

  [UFAGL].[SKLAD] AS [Sklad],

  [UFAGL].[TIP] AS [Knj]

  FROM UFAGL)

  -- return only needed page of records from tmp

  SELECT [St] ,

  [Sklad],

  [Knj]

  FROM tmp

  WHERE RecID BETWEEN

  (((@PageNumber-1) * @RowsPerPage) + 1)

  AND

  (@PageNumber * @RowsPerPage)

  --**************************** PART 3 *****************************

  --Return values:

  SELECT @Pages -- return number of pages

  SELECT @PageNumber -- return actual page

  SELECT @Records -- return number of total records

  2. method:

  --- big thanks goes to Jacob Sebastian, creator of this algorithm

  --- regards Davor Geci

  -------------------------------------------------------------------------

  -------------------------------------------------------------------------

  -------------------------------------------------------------------------

  -------------------------------------------------------------------------

  -------------------------------------------------------------------------

  --2. if you only want to retrive specific page (@PageNumber)

  -------------------------------------------------------------------------

  -------------------------------------------------------------------------

  -------------------------------------------------------------------------

  DECLARE @PageNumber AS BIGINT = 2;

  DECLARE @Pages AS FLOAT = 0;

  DECLARE @RowsPerPage AS FLOAT = 100;

  DECLARE @Records AS FLOAT = 0;

  --**************************** PART 1 *****************************

  -- if you have WHERE or JOINS put also here for accurate results

  SELECT @Records = COUNT(*)

  FROM UfaGl

  SET @Pages = ceiling (@Records/@RowsPerPage) ; -- get number of total pages

  --**************************** PART 2 *****************************

  -- this is the acctual sql for getting my records ordered by order by field

  -- if you have WHERE or JOINS put also here for accurate results

  WITH tmp AS

  (SELECT ROW_NUMBER () OVER (ORDER BY UFAGL.BRRAC DESC ) AS RecID ,

  [UFAGL].[STATUS] AS [St] ,

  [UFAGL].[SKLAD] AS [Sklad],

  [UFAGL].[TIP] AS [Knj]

  FROM UFAGL)

  -- return only needed page of records from tmp

  SELECT [St] ,

  [Sklad],

  [Knj]

  FROM tmp

  WHERE RecID BETWEEN

  (((@PageNumber-1) * @RowsPerPage) + 1)

  AND

  (@PageNumber * @RowsPerPage)

  --**************************** PART 3 *****************************

  --Return values:

  SELECT @Pages -- return number of pages

  SELECT @PageNumber -- return actual page

  SELECT @Records -- return number of total records

• dvdbeahm

  Grasshopper

  Points: 17

  More actions

  April 26, 2010 at 6:10 am

  #1157549

  You should look at this solution: http://www.15seconds.com/Issue/070628.htm

  It is a more generic version of what you are looking for. It does paging in terms of page number and # of records per page, supports sorting and filtering, and can be applied to virtually any table or view.

  It also returns the total # of records being paged, as many posters were requesting.