How to Create a Stored Procedures with Dynamic Search & Paging (Pagination)

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
        @lContactID         INT,
        @lFirstName         NVARCHAR(50),
        @lLastName          NVARCHAR(50),
        @lEmailAddress      NVARCHAR(50),
        @lEmailPromotion    INT,
        @lPhone             NVARCHAR(25)
        @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 (
        FROM Person.Contact
            (@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)
    FROM CTE_Results AS CPC
        ROWNUM > @lFirstRec
    AND ROWNUM < @lLastRec

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 :

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s