Stored procedure for simple paging

Run the following script to create the sample table and insert records.

create table Employee
(
  EmployeeID int primary key identity,
  EmpName varchar(200),
  Address varchar(200)
)

insert into Employee values('Ahmad','Add 1')
insert into Employee values('Mahmood','Add 2')
insert into Employee values('Zahir','Add 3')
insert into Employee values('Aziz','Add 4')
insert into Employee values('Khalid','Add 5')
insert into Employee values('Mirwaise','Add 6')
insert into Employee values('Inayat','Add 2')
insert into Employee values('Jamil','Add 3')
insert into Employee values('Bari','Add 4')
insert into Employee values('Qureshi','Add 1')
insert into Employee values('Farooq','Add 9')
insert into Employee values('Qazi','Add 8')
insert into Employee values('massod','Add 13')
insert into Employee values('Fahim','Add 8')

Run the following script to create the stored procedure

create proc [dbo].[SelectEmployee_Paging]
@pageNo int,
@pageSize int
as
declare @fromSr int
declare @toSr int
if(@pageNo=1)
begin
 set @fromSr=@pageNo
 set @toSr=@pageNo*@pageSize
end else 
begin
 set @fromSr=(@pageNo-1)*@pageSize+1
 set @toSr=@pageNo*@pageSize
end

;with tbl as (
SELECT    row_number() over(order by EmployeeID) as srno, EmployeeID, EmpName
from Employee
) 
select  *,(select count(*) from tbl) as TotalRecords from tbl where srno>=@fromSr and srno<=@toSr

Execute Stored procedure to view the desired Result

-- '1st Page. with 3 records per page'
exec SelectEmployee_Paging 1,3
-- 4th page. with single record per page
exec SelectEmployee_Paging 4,1
-- 2nd page with 4 records per page
exec SelectEmployee_Paging 2,4