Saturday, February 18, 2012

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 WFirms
   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 WFirms
      order by 1

      set rowcount @p_page_size
      select * from WFirms
      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,
         Firmname,
         Num
      FROM Wfirms
)

SELECT Num, FirmName
  FROM OrdersRN
 WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
                  AND @PageNum * @PageSize
 ORDER BY Num

No comments: