I used this article as a reference for my very first stored procedure for my internship project so I would like to share it with you if you are looking for some ideas to start a stored procedure with search and pagination.
How can we apply paging (or pagination) on the returned recordsets from a Stored Procedures?
Pagination is required when a 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 on 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
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'
Retrived from : https://sqlwithmanoj.com/2011/12/30/creating-stored-procedures-with-dynamic-search-paging-pagination/