Creating Stored Procedures with Dynamic Search & Paging (Pagination)

In my previous post we learned how we can apply Dynamic Search capabilities to an SP.

Here we will see how can we apply paging (or pagination) on the returned recordsets from a Stored Procedures.

Pagination is required when lot of records are returned from an SP and the webpage becomes very heavy to load it, like more than 1k records. And it also becomes difficult for a user to handle that much records and view in a single screen.

Many webpages displaying records has First, Previous, Next & Last buttons on top and bottom of the data grid. So to make the buttons function we’ve to implement the paging functionality on SPs itself.

Let’s check with a sample code using [Person].[Contact] Table of [AdventureWorks] Database: