Tag: stored procedure

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/