SP Search & Paging

Creating Stored Procedures with Dynamic Search & Paging (Pagination)

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 INTSET @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

Testing codes

— 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’