Paging in Database

Paging in records from database. Some time you need to get number or recording at a time from large number of records. Just as .NET has gridview control with paging utility. So some time we need to create own logic for paging in records at database leve. So below is the code to accomplish that task.

/*

* Below code is simple to understand..

* */

DECLARE @employees AS TABLE (EmpId INT IDENTITY(1,1), EmpName VARCHAR(20), Salary INT)

INSERT INTO @employees(EmpName, Salary)

VALUES ('Sohan', 2000), ('Rohan', 4000), ('Mohan', 3000), ('Ram', 2000), ('Seema', 3000),('Micky', 2500), ('Rinky', 4000),

('Ronin', 5000), ('Robin', 2000), ('Sam', 4000), ('Sohil', 3000), ('Raven', 2000),

('Gery', 5000), ('Bob', 2000), ('Denis', 4000), ('Steph', 3000), ('Jhon', 2000),

('Kaleb', 3000), ('Rahul', 2000), ('Reena', 3000),('Kamal', 2500), ('Vimal', 4000)

DECLARE @records INT = @@ROWCOUNT, @pager INT = 10

/* Below code gives you total number of pages based on pager size here @pager = 10 */

SELECT CASE WHEN (@records/@pager = @records%@pager) THEN (@records/@pager) + 1

ELSE (@records/10) END Pages

DECLARE @index INT = 1

DECLARE @start INT , @end INT

IF @index = 1 /* For first time pass index as 1 */

BEGIN

      SELECT @start = 1, @end = @pager

END

ELSE

BEGIN /* This works for all the time accept first time i.e. index <> 1 */

      SET @end = (@index * @pager)

      SET @start = (@end - 9)

END  

SELECT * FROM

(

      SELECT *, ROW_NUMBER() OVER (ORDER BY EmpId ASC) AS Pager FROM @employees

) a

WHERE a.Pager BETWEEN @start AND @end