Paging with OFFSET and FETCH in SQL 2012

Paging with OFFSET and FETCH

 

Created a sample table with few records. @PAGESIZE variable gives the no of records needed in each page, @STARTINGROW variable gives the starting record count, @SORTCONDITION gives the sorting attribute and order (functional requirement).

 

DECLARE @PAGESIZE INT

DECLARE @STARTINGROW INT

DECLARE @SORTCONDITION VARCHAR(250)

 

SET @PAGESIZE = 20

SET @STARTINGROW = 10 -1

SET @SORTCONDITION = 'EMPNO ACS'

 

SELECT        R1.ID 

,             R1.EMPNO 

,             R1.EMPNAME

,             R1.EMPADD1

FROM   SAMPLEFETCH R1       (NOLOCK)

ORDER BY

              CASE   @SORTCONDITION

              WHEN   'EMPNO ACS' THEN EMPNO

              WHEN   'EMPNAME ASC' THEN EMPNAME

              WHEN   'EMPADD1 ASC' THEN EMPADD1

              END ASC,

              CASE   @SORTCONDITION

              WHEN   'EMPNO DESC' THEN EMPNO

              WHEN   'EMPNAME DESC' THEN EMPNAME

              WHEN   'EMPADD1 DESC' THEN EMPADD1

              END DESC

OFFSET @STARTINGROW ROWS

FETCH NEXT @PAGESIZE ROWS ONLY