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
)
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/

The Coding Naming Convention

How to make your colleagues’ lives easier while reviewing your codes?

– Please just follow the naming convention.

For me personally following the Naming Convention is a basic manner and a friendly way to treat someone who will read your code. It reflects that you have attention to detail and logical thinking. It’s also one of the criteria to determine your coding quality.

My training manager had said something about this, ‘I will see whether you are a professional developer even your code doesn’t even work’.

Some bad examples and some tips:

  • Do not give full and descriptive names. eg. FController or FinController instead of FinanceController. Avoid using unknown acronyms is necessary.
  • Pascal Casing is important. E.g. ProductCategory, ChildProgram
  • Do not provide comments on the code which may look confusing. Provide comments on your assumptions is effective.
  • Use new lines to indicate a new logical group.
  • Always Indent the code properly
  • Use Ctrl+K+D to auto-indent your code in Visual Studio.
  • Retain consistency in styles.