Server Side Paging With SQL Server 2005

Question

Post reply

Server Side Paging With SQL Server 2005

Viewing 2 posts - 61 through 61 (of 61 total)

You must be logged in to reply to this topic. Login to reply

Davor Geci SSC-Addicted Points: 487 More actions · Answer 1

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 · Answer 2

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.