Stored Procedure for Paging in SQL

Implementing paging correctly is critically important. If you’re shop still uses, or insists on stored procedures, that’s fine. It’s just as easy to write a stored procedure for paging in SQL.

This example was developed on Microsoft SQL server 2016, although I believe it will work fine back to SQL 2012, not sure about SQL 2008.

As with any solution, the idea is to keep the data at rest and only pull out what you need to return. This example uses the AdventureWorks database.

This example has all of the typical parameters that I use on many paging implementations. You can trim out the ones not needed for your implementation.

USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Created by Jack Yasgar, www.jackyasgar.net
CREATE PROCEDURE [dbo].[uspGetOrderDetailsYTGI]
	@PageNumber			INT,
	@PageSize			INT,
	@SearchTerm			VARCHAR(100) = NULL,
	@SortDirection		VARCHAR(4) = 'ASC',
	@SortColumn			VARCHAR(100) = 'lastname',
	@Filter				VARCHAR(50) = '',
	@ActiveOnly			BIT
AS
BEGIN
    SET NOCOUNT ON;

 	IF ((@PageNumber = 0) OR (@PageNumber IS NULL))
	BEGIN
		SET @PageNumber = 1;
	END

	IF ((@PageSize = 0) OR (@PageSize IS NULL))
	BEGIN
		SET @PageSize = 25;
	END

	DECLARE @FirstRec INT, @LastRec INT;
	DECLARE @OnlineOrder BIT;
	SET @OnlineOrder = (SELECT CONVERT(BIT, @Filter));

	SELECT @FirstRec = (@PageNumber - 1) * @PageSize
	SELECT @LastRec = (@PageNumber * @PageSize + 1);
	

	SELECT 
		sod.OrderQty,
		sod.ProductID,
		p.[Name] AS ProductName,
		p.ProductNumber,
		soh.ShipDate,
		sod.UnitPrice,
		soh.OnlineOrderFlag
	FROM [Sales].[SalesOrderHeader] soh WITH (NOLOCK)
		LEFT JOIN [Sales].[SalesOrderDetail] sod WITH (NOLOCK) ON soh.SalesOrderID = sod.SalesOrderID
		LEFT JOIN [Production].[Product] p WITH (NOLOCK) ON p.ProductID = sod.ProductID
	WHERE 
		(ISNULL(@SearchTerm, '') = '' OR @SearchTerm = '' OR p.[Name] LIKE '%' + @SearchTerm + '%')
		AND
		(ISNULL(@Filter, 0) = 0 OR @Filter = 0 OR soh.OnlineOrderFlag = @OnlineOrder)
		AND
		(ISNULL(@ActiveOnly, 0) = 0 OR @ActiveOnly = 0 OR soh.ShipDate > '2013-01-01')
	ORDER BY 
		CASE WHEN @SortDirection = 'ASC' THEN
			CASE @SortColumn 
				WHEN 'ProductName'		THEN p.[Name]
				WHEN 'ProductNumber'	THEN p.ProductNumber
			END
		END,
		CASE WHEN @SortDirection = 'DESC' THEN
			CASE @SortColumn 
				WHEN 'ProductName'		THEN p.[Name]
				WHEN 'ProductNumber'	THEN p.ProductNumber
			END
		END DESC
	OFFSET @PageSize * (@PageNumber - 1) ROWS
	FETCH NEXT @PageSize ROWS ONLY;

		-- Return the full count for pager
	SELECT COUNT(*) AS TotalCount FROM (
			SELECT DISTINCT sod.SalesOrderDetailID
			FROM [Sales].[SalesOrderHeader] soh WITH (NOLOCK)
				LEFT JOIN [Sales].[SalesOrderDetail] sod WITH (NOLOCK) ON soh.SalesOrderID = sod.SalesOrderID
				LEFT JOIN [Production].[Product] p WITH (NOLOCK) ON p.ProductID = sod.ProductID
			WHERE 
				(ISNULL(@SearchTerm, '') = '' OR @SearchTerm = '' OR p.[Name] LIKE '%' + @SearchTerm + '%')
				AND
				(ISNULL(@Filter, 0) = 0 OR @Filter = 0 OR soh.OnlineOrderFlag = @OnlineOrder)
				AND
				(ISNULL(@ActiveOnly, 0) = 0 OR @ActiveOnly = 0 OR soh.ShipDate > '2013-01-01')
		) AS Counting

END

This query uses the SQL OFFSET command to start where we need to based upon the PageNumber and PageSize values passed in as arguments. It then uses the FETCH command to retrieve a specific row. You can take a look at the documentation on FETCH here:

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/fetch-transact-sql?view=sql-server-ver16

Once you receive back the DataSet in your Repository, you’ll need to stuff the data into a collection of DTO’s in your ServiceLayer for transfer through your web service: Stuffing DataSets into your objects.

Author: Jack Yasgar

Jack Yasgar has been developing software for various industries for two decades. Currently, he utilizes C#, JQuery, JavaScript, SQL Server with stored procedures and/or Entity Framework to produce MVC responsive web sites that converse to a service layer utilizing RESTful API in Web API 2.0 or Microsoft WCF web services. The infrastructure can be internal, shared or reside in Azure. Jack has designed dozens of relational databases that use the proper primary keys and foreign keys to allow for data integrity moving forward. While working in a Scrum/Agile environment, he is a firm believer that quality software comes from quality planning. Without getting caught up in analysis paralysis, it is still possible to achieve a level of design that allows an agile team to move forward quickly while keeping re-work to a minimum. Jack believes, “The key to long term software success is adhering to the SOLID design principles. Software written quickly, using wizards and other methods can impress the business sponsor / product owner for a short period of time. Once the honeymoon is over, the product owner will stay enamored when the team can implement changes quickly and fix bugs in minutes, not hours or days.” Jack has become certified by the Object Management Group as OCUP II (OMG Certified UML Professional) in addition to his certification as a Microsoft Certified Professional. The use of the Unified Modeling Language (UML) provides a visual guide to Use Cases and Activities that can guide the product owner in designing software that meets the end user needs. The software development teams then use the same drawings to create their Unit Tests to make sure that the software meets all those needs. The QA testing team can use the UML drawings as a guide to produce test cases. Once the software is in production, the UML drawings become a reference for business users and support staff to know what decisions are happening behind the scenes to guide their support efforts.

Leave a Reply

Your email address will not be published. Required fields are marked *