T SQL paging stored procedure

Paging is a process of splitting large result set into subset of pages. So, instead of displaying 5000 records resulting from a search, first 20 records are shown and then next 20 and so on. A t sql paging stored procedure example is shown below.

In SQL Server 2005, Common Table Expression (CTE) is generally used to implement paging feature.

Use AdventureWorks2014
@PageNumber INT=1, 
@PageSize   INT=10
WITH ctepaging 
     AS (SELECT *,
                Row_number() OVER(ORDER BY lastname, firstname) AS rownum 
         FROM Person.Person) 
FROM   ctepaging 
WHERE  rownum BETWEEN ( @PageNumber - 1 ) * @PageSize + 1 AND
@PageNumber * @PageSize

The above query creates a stored procedure usp_paging with 2 parameters. The @pagenumber specifies the page number to display the result for and @pagesize is the number of rows to be returned. So for example, to get data for page 2 with 10 records the procedure is executed as below

1_t sql paging stored procedure


In SQL Server 2012, MS has come up with a modified ORDER BY clause to implement the same. This is done using two new keywords OFFSET and FETCH. Offset tells the number of rows to skip and Fetch tells number of rows to be returned after offset clause is processed. So, offset 0, Fetch 10 will return 10 rows starting from 1 and an offset 2, Fetch 10 will return 10 rows starting from 3. Given below is an example stored procedure to implement paging in SQL Server 2012.

CREATE PROCEDURE usp_pagingin2012
@Start     INT=0, 
@PageLimit INT=10
SELECT * FROM Person.Person
ORDER  BY LastName,FirstName

The above query creates a procedure to returns 10 rows starting from 1 to 10. To get next 10 rows, set the @start parameter to 10 and so on.  The output of the procedure with 5 records page limit is shown below

2_t sql paging stored procedure

The procedure returns first 5 records with @start=0 and @pagelimit=5 and the next of records when @start=5 and @pagelimit=5 and on so on….


Like us on FaceBook Join the fastest growing SQL Server group on FaceBook


2 Comments on “T SQL paging stored procedure”

  1. I like you approach…but if filter are present than it won’t give the data till the page size. It will go to the page no and give the result back…what can be the solution for that?

Leave a Reply

Your email address will not be published.