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:
CREATE PROCEDURE USP_GET_Contacts_DynSearch_Paging ( -- Pagination @PageNbr INT = 1, @PageSize INT = 10, -- Optional Filters for Dynamic Search @ContactID INT = NULL, @FirstName NVARCHAR(50) = NULL, @LastName NVARCHAR(50) = NULL, @EmailAddress NVARCHAR(50) = NULL, @EmailPromotion INT = NULL, @Phone NVARCHAR(25) = NULL ) AS BEGIN DECLARE @lContactID INT, @lFirstName NVARCHAR(50), @lLastName NVARCHAR(50), @lEmailAddress NVARCHAR(50), @lEmailPromotion INT, @lPhone NVARCHAR(25) DECLARE @lPageNbr INT, @lPageSize INT, @lFirstRec INT, @lLastRec INT, @lTotalRows INT SET @lContactID = @ContactID SET @lFirstName = LTRIM(RTRIM(@FirstName)) SET @lLastName = LTRIM(RTRIM(@LastName)) SET @lEmailAddress = LTRIM(RTRIM(@EmailAddress)) SET @lEmailPromotion = @EmailPromotion SET @lPhone = LTRIM(RTRIM(@Phone)) SET @lPageNbr = @PageNbr SET @lPageSize = @PageSize SET @lFirstRec = ( @lPageNbr - 1 ) * @lPageSize SET @lLastRec = ( @lPageNbr * @lPageSize + 1 ) SET @lTotalRows = @lFirstRec - @lLastRec + 1 ; WITH CTE_Results AS ( SELECT ROW_NUMBER() OVER (ORDER BY ContactID) AS ROWNUM, ContactID, Title, FirstName, MiddleName, LastName, Suffix, EmailAddress, EmailPromotion, Phone FROM Person.Contact WHERE (@lContactID IS NULL OR ContactID = @lContactID) AND (@lFirstName IS NULL OR FirstName LIKE '%' + @lFirstName + '%') AND (@lLastName IS NULL OR LastName LIKE '%' + @lLastName + '%') AND (@lEmailAddress IS NULL OR EmailAddress LIKE '%' + @lEmailAddress + '%') AND (@lEmailPromotion IS NULL OR EmailPromotion = @lEmailPromotion) AND (@lPhone IS NULL OR Phone = @lPhone) ) SELECT ContactID, Title, FirstName, MiddleName, LastName, Suffix, EmailAddress, EmailPromotion, Phone FROM CTE_Results AS CPC WHERE ROWNUM > @lFirstRec AND ROWNUM < @lLastRec ORDER BY ROWNUM ASC END GO
OK, now let’s test this SP:
-- No parameters provided, fetch first 10 default records: EXEC USP_GET_Contacts_DynSearch_Paging -- On providing @PageSize=20, will fetch 20 records: EXEC USP_GET_Contacts_DynSearch_Paging @PageSize=20 -- On providing @PageNbr=2, @PageSize=10, will display second page, ContactID starting from 11 to 20: EXEC USP_GET_Contacts_DynSearch_Paging @PageNbr=2, @PageSize=10 -- On providing @PageNbr=1, @PageSize=50, @FirstName = 'Sam', it will search FurstName like Sam and will fetch first 50 records: EXEC USP_GET_Contacts_DynSearch_Paging @PageNbr=1, @PageSize=50, @FirstName = 'Sam'
SQL Server 2012 (a.k.a Denali) has a new mechanism to implement pagination by using OFFSET FETCH clause, check it here in my blog post.
Appreciate your valuable comments if we can do this in a different or/and better way.