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.

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.

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….



Ahmad Osama

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

Follow me on TwitterFollow me on FaceBook