Advertisements

Archive

Posts Tagged ‘SQL Pagination’

Creating Stored Procedures with Dynamic Search & Paging (Pagination)

December 30, 2011 4 comments

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.

Advertisements

SQL Server 2012 (DENALI) TSQL – New feature | OFFSET FETCH Clause (for paging/pagination)

July 28, 2011 3 comments

As per MS BOL, the new Denali’s OFFSET-FETCH Clause provides an option to fetch only a window or page of a fix set of results from the result set. OFFSET-FETCH can be used only with the ORDER BY clause.

This was the most awated feature for the frontend/GUI developers to display volumnous data in a small grid, page by page. Prior to this they used to devise not so complex but a bit complex SQL logic to display records page by page. Introduction of this feature has limited the complex logic to a few lines of single SQL statement which is much more optimized.

Let’s see how can we use this feature:

--// Example #1:

-- This will skip first 100 records and show next 20 records
SELECT BusinessEntityID,  LoginID, JobTitle
FROM HumanResources.Employee
ORDER BY BusinessEntityID
OFFSET 100 ROWS
FETCH NEXT 20 ROWS ONLY;
GO

--// Example #2:

-- Start from first record and show next 10 records
SELECT BusinessEntityID,  LoginID, JobTitle
FROM HumanResources.Employee
ORDER BY BusinessEntityID
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

-- Skip first 10 records and show next 10 records
SELECT BusinessEntityID,  LoginID, JobTitle
FROM HumanResources.Employee
ORDER BY BusinessEntityID
OFFSET 10 ROWS -- To show next page of 10 records, just change the offset by adding the page size, i.e 10.
FETCH NEXT 10 ROWS ONLY;
GO

--// Example #3:

-- To use this in front-end, the above logic can be made dynamic by using a few variables, like:
DECLARE @StartRec INT
DECLARE @PageSize INT
DECLARE @RecordEnd INT

SET @StartRec = 0
SET @PageSize = 10
SET @RecordEnd = @PageSize

WHILE @RecordEnd <> 0 -- I'm using WHILE loop to simulate it here
BEGIN
	SELECT BusinessEntityID,  LoginID, JobTitle
	FROM HumanResources.Employee
	ORDER BY BusinessEntityID
	OFFSET @StartRec ROWS
	FETCH NEXT @PageSize ROWS ONLY

	SET @RecordEnd = @@ROWCOUNT -- Exit loop at 0

	SET @StartRec = @StartRec + @PageSize
END
GO

Rules to use OFFSET FETCH (via MSDN):
1. ORDER BY is mandatory to use OFFSET and FETCH clause.
2. OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
3. TOP cannot be combined with OFFSET and FETCH in the same query expression.
4. The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries.

More on MSDN, link: http://msdn.microsoft.com/en-us/library/gg699618%28v=sql.110%29.aspx