Paging in SQL Server

CREATE proc P_GET_PAGE

(

@p_page int,

@p_page_size int

)

as

begin

set nocount on

declare @total_rows int

declare @first_row int

declare @first_row_id int

select @total_rows = count(Num) from Users

select @first_row = (@p_page - 1) * @p_page_size + 1

if (@first_row <= @total_rows)

begin

set rowcount @first_row

select @first_row_id = Num

from Users

order by 1

set rowcount @p_page_size

select * from Users

where Num >= @first_row_id

order by 1

end

set nocount off

end

exec P_GET_PAGE 10, 10



DECLARE @PageNum AS INT;

DECLARE @PageSize AS INT;

SET @PageNum = 1;

SET @PageSize = 10;

WITH OrdersRN AS

(

SELECT ROW_NUMBER() OVER(ORDER BY Num) AS RowNum,

 UserID,

 UserName

FROM Users

)

SELECT UserID, UserName

FROM OrdersRN

WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1

AND @PageNum * @PageSize

ORDER BY Num