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