SQL with Manoj

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.