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
GO
CREATE PROCEDURE usp_paging
@PageNumber INT=1, 
@PageSize   INT=10
AS 
BEGIN
WITH ctepaging 
     AS (SELECT *,
                Row_number() OVER(ORDER BY lastname, firstname) AS rownum 
         FROM Person.Person) 
SELECT * 
FROM   ctepaging 
WHERE  rownum BETWEEN ( @PageNumber - 1 ) * @PageSize + 1 AND
@PageNumber * @PageSize
END

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

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
AS
BEGIN
SELECT * FROM Person.Person
ORDER  BY LastName,FirstName
OFFSET @Start ROW
FETCH NEXT @PageLimit ROWS ONLY
END

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

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