Displaying records from the database in gridview based on the page and not fetching all the records from the database.

Here is the way for accessing the records from the database and displaying it in the gridview.
You can use custom paging for navigating withing the pages of the gridview.


EXEC  usp_GetRecords 2,5  -- this will fetch only 5 records starting from 6 to 10

CREATE PROCEDURE usp_GetRecords
(
 @iPageNumber INT,  -- this is the page number sent during pagination
 @iNumberOfRecords INT -- this is the number of records displayed per page
)
AS
DECLARE @iStartNumber INT
DECLARE @iEndNumber INT
SET @iEndNumber = (@iPageNumber * @iNumberOfRecords)
SET @iStartNumber = (@iEndNumber - @iNumberOfRecords) + 1;
WITH cte_t1(rownumber,id,a,b)
AS
(
 SELECT ROW_NUMBER() OVER(ORDER BY id) AS rownumber,id,a,b FROM t1
)
SELECT * FROM cte_t1
WHERE rownumber BETWEEN @iStartNumber AND @iEndNumber