← back to the blog


T-SQL Paging Sample

Posted in SQL by dake

This is old way, there's better way to do it, but kind of sampel example of how to do pagination at sql level

declare @requestMaxReturnItems int
declare @requestPageNumber int
declare @startRow int
declare @endRow int
declare @totalPageByMaxReturnNumber int
declare @totalCount int;

-- temp test

-- request from ui
-- max items return from this sql
set @requestMaxReturnItems = 10;
-- which page number to return
set @requestPageNumber = 1
--set @startRow = 1
--set @endRow = 10

select @totalCount = count(*) FROM [OrangeGrid].[dbo].[DiagnosticLog];
set @totalPageByMaxReturnNumber = @totalCount / @requestMaxReturnItems;
set @startRow = (@requestPageNumber - 1) * @requestMaxReturnItems;
set @endRow = @requestPageNumber * @requestMaxReturnItems;

print @totalPageByMaxReturnNumber

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY YourTable.CreateDate desc ) AS RowNum, *
          FROM      YourTable
        ) AS t
WHERE   RowNum > @startRow
    AND RowNum <= @endRow
ORDER BY RowNum