Basically, we do not need to change the
/HTML table/etc. where we display the data to create an effective paging.
What we need is only to change the SQL script to get a certain record and add some data paging controls on the UI.
For example, a paging has 10 rows in a page and we want to show the second page (record number 11-20). The SQL script will be:
DECLARE @fromDate DATETIME
DECLARE @toDate DATETIME
DECLARE @currentPage INT
DECLARE @pageSize INT
DECLARE @startRowNumber INT
DECLARE @endRowNumber INT
SET @fromDate = '2011-01-01'
SET @toDate = '2011-12-31'
SET @currentPage = 2
SET @pageSize = 10
SET @startRowNumber = (@currentPage - 1) * @pageSize + 1
SET @endRowNumber = @currentPage * @pageSize
SELECT tmp.TotalRecords, tmp.TransDate, tmp.TransDesc, tmp.Amount
COUNT(1) OVER() AS TotalRecords,
ROW_NUMBER() OVER(ORDER BY TransDate DESC) AS RowNumber,
TransDate, TransDesc, Amount
FROM MainTrans WITH(NOLOCK) WHERE TransDate BETWEEN @fromDate AND @toDate
WHERE tmp.RowNumber BETWEEN @startRowNumber AND @endRowNumber
The example above will return 10 records, which are records number 11-20 based on
descending order (assume table has 20 or more records).
returns total number of records available between
, which can be used to calculate the total pages (
TotalPages = TotalRecords / PageSize
ROW_NUMBER() OVER(ORDER BY <Column Name> DESC)
The above syntax will return row number for each row with ordering according to
The above syntax will return total records selected, which
Robby Tendean had his Master Degree in Computer Science from National Taiwan University of Science and Technology. His master degree thesis, Energy-Efficient Routing Protocol for Wireless Sensor Networks with Static Clustering and Dynamic Structure, has been published in Springerlink International Journal.