---

Search posts under SQL Server:

---

pagination stored procedure in SQL Server

How to create stored procedure to return paginated data (custom paginations) from SQL Server database?

To get the paginated data from stored procedure, we need to pass at least three parameters if the paginated data doesn’t need any filtration.

• startRowIndex – start fetching records from which row number
• pageSize – number of records to fetch
• totalCount – total count of records into the database table that will be used to render the page number links on the web page so that user will be able to navigate to different pages.

CREATE PROCEDURE GetPersonalDetailsPaginated
       -- Add the parameters for the stored procedure here
       @startRowIndex int,
       @pageSize int,
       @totalCount int OUT
AS
BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements. 
       SET NOCOUNT ON;

       -- Get the paginated records
       SELECT PersonalDetailsId, FirstName, LastName, Age, Active FROM
PersonalDetails -- keep WHERE clause here if any
       ORDER BY PersonalDetailsId
       OFFSET @startRowindex ROWS FETCH NEXT @pageSize ROWS ONLY

       -- get the total count of the records
       SELECT @totalCount = COUNT(PersonalDetailsId) FROM PersonalDetails
              -- keep the same WHERE clause as above

Note that we have @startRowIndex and @pageSize as normal parameters (input parameter) and @totalCount as OUT (output parameter) whose value should be set inside the stored procedure.

Write above SQL code into the query window and execute it that will create the stored procedure. In this stored procedure, we are first selecting PersonalDetails records ordered by PersonalDetailsId

and then using OFFSET (skip following number of records) the value of @startRowIndex and the FETCH NEXT @pageSize rows from the PersonalDetials table.

The next SELECT statement is setting the value of the @totalCount.

Now when we Execute Stored procedure by right clicking the stored procedure name, we see that the first result set shows as the paginated record and the second result as the totalCount value that is nothing but the total count of the records in the PersonalDetails database table.