Getting the Rows from DB in page wise using Row_Number in SQL Server

Create table #States(StateID INT,StateName NVARCHAR(20))
INSERT INTO #States
SELECT 1,'California' UNION
SELECT 2,'Albama' UNION
SELECT 3,'Alaska' UNION
SELECT 4,'Arizona' UNION
SELECT 5,'Florida' UNION
SELECT 6,'Hawaii' UNION
SELECT 7,'Montana' UNION
SELECT 8,'Indiana' UNION
SELECT 9,'Lowa' UNION
SELECT 10,'New York' UNION
SELECT 11,'New Mexico' UNION
SELECT 12,'Texas' UNION
SELECT 13,'Virginia' UNION
SELECT 14,'South Carolina' UNION
SELECT 15,'Washington'


SELECT * FROM #States
  

Create the Procedure like

Create Proc GetRecords(@pageNo int=1,@pageSize int)
as begin
Select StateID,StateName from
(Select Row_Number() over (Order By StateID) SNO,StateID,StateName from #States)S
Where S.SNO Between ((@pageNo-1)*@pageSize)+1 and @pageNo*@pageSize
end

Execution:-

exec GetRecords 1,4        --1 is page number & 4 is page size
exec GetRecords 2,4
exec GetRecords 3,4
exec GetRecords 4,4

o/p:

DROP TABLE #States

Tag: Using Row_Number in SQL Server finding records