How to create procedure for pagging in sql server with OFFSET? [closed]

I want to create a stored procedure to fetch data by PageIndex and PageSize using OFFSET ROWS

Answer

You can use OFFSET and FETCH NEXT for tech number of rows from index. Demo is here

CREATE PROCEDURE [dbo].[P_StudentSelectPaging]
@PageIndex          INT=1,
@PageSize           INT=10 
AS 
BEGIN   

   SET NOCOUNT ON;  
   SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS RowNumber,[RecordCount]=COUNT(*) OVER(),
         Id,Name,City   
   FROM (
        SELECT          
            ISNULL(Id,0) AS Id,
            ISNULL(Name,0) AS Name,             
            ISNULL(City,'') AS City     
        FROM F_Posts P      
   ) AS tmpTable
   ORDER BY ISNULL(Id,1) DESC       

   OFFSET @PageSize * (@PageIndex-1) ROWS   
   FETCH NEXT @PageSize ROWS ONLY;              
END

Note: OFFSET is only supported in the higher versions than SQL Server R2 2008.