Stored Procedure with Sorting, Paging, and Filtering

In day to day work, we often need data to be displayed in Grid that needs sorting, paging and filtering on User Interface, whether web or Windows. I often struggle to find a unified solution. I found that the best way is to have fetch data in required format from stored procedure and just have presentation logic on UI rather than mixing a lot of logic for Sorting, Paging and Filtering. Here, I am presenting a simple approach to have data from Stored Procedure in required format based on given parameters.

CREATE PROCEDURE USP_SEL_Contacts
(
    /* 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,
  /*– Pagination Parameters */
  @PageNo INT = 1,
  @PageSize INT = 10,
   /*– Sorting Parameters */
   @SortColumn NVARCHAR(20) = ‘Title’,
   @SortOrder NVARCHAR(4)=‘ASC’
)
AS
BEGIN
    /*–Declaring Local Variables corresponding to parameters for modification */
    DECLARE 
    @lContactID INT,
    @lFirstName NVARCHAR(50),
    @lLastName NVARCHAR(50),
    @lEmailAddress NVARCHAR(50),
    @lEmailPromotion INT,
    @lPhone NVARCHAR(25),

    @lPageNbr INT,
    @lPageSize INT,
    @lSortCol NVARCHAR(20),
    @lFirstRec INT,
    @lLastRec INT,
    @lTotalRows INT

    /*Setting Local Variables*/
    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 = @PageNo
    SET @lPageSize = @PageSize
    SET @lSortCol = LTRIM(RTRIM(@SortColumn))

    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
        CASE WHEN (@lSortCol = ‘ContactID’ AND @SortOrder=‘ASC’)
                    THEN ContactID
        END ASC,
        CASE WHEN (@lSortCol = ‘ContactID’ AND @SortOrder=‘DESC’)
                  THEN ContactID
        END DESC,

        CASE WHEN (@lSortCol = ‘Title’ AND @SortOrder=‘ASC’)
                  THEN Title
        END ASC,
        CASE WHEN @lSortCol = ‘Title’ AND @SortOrder=‘DESC’
                THEN Title
        END DESC,

        CASE WHEN @lSortCol = ‘FirstName’ AND @SortOrder=‘ASC’
                THEN FirstName
        END ASC,
        CASE WHEN @lSortCol = ‘FirstName’ AND @SortOrder=‘DESC’
                THEN FirstName
        END DESC,

        CASE WHEN @lSortCol = ‘MiddleName’ AND @SortOrder=‘ASC’
                THEN MiddleName
       END ASC,
        CASE WHEN @lSortCol = ‘MiddleName’ AND @SortOrder=‘DESC’
              THEN MiddleName
       END DESC,

        CASE WHEN @lSortCol = ‘LastName’ AND @SortOrder=‘ASC’
              THEN LastName
        END ASC,
       CASE WHEN @lSortCol = ‘LastName’ AND @SortOrder=‘DESC’
              THEN LastName
        END DESC,

       CASE WHEN @lSortCol = ‘Suffix’ AND @SortOrder=‘ASC’
            THEN Suffix
       END ASC,
       CASE WHEN @lSortCol = ‘Suffix’ AND @SortOrder=‘DESC’
            THEN Suffix
       END DESC,

       CASE WHEN @lSortCol = ‘EmailAddress’ AND @SortOrder=‘ASC’
              THEN EmailAddress
        END ASC,
        CASE WHEN @lSortCol = ‘EmailAddress’ AND @SortOrder=‘DESC’
              THEN EmailAddress
        END DESC,

        CASE WHEN @lSortCol = ‘EmailPromotion’ AND @SortOrder=‘ASC’
              THEN EmailPromotion
        END ASC,
        CASE WHEN @lSortCol = ‘EmailPromotion‘ AND @SortOrder=‘DESC’
            THEN EmailPromotion
        END DESC,

        CASE WHEN @lSortCol = ‘Phone’ AND @SortOrder=’ASC’
            THEN Phone
        END ASC,
        CASE WHEN @lSortCol = ‘Phone’ AND @SortOrder=’DESC’
            THEN Phone
        END DESC

  ) AS ROWNUM,
  Count(*) over () AS TotalCount,
  ContactID,
  Title,
  FirstName,
  MiddleName,
  LastName,
  Suffix,
  EmailAddress,
  EmailPromotion,
  Phone
 FROM 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 LIKE ‘%’ +@lPhone+ ‘%’)
)
SELECT
    TotalCount,
    ROWNUM,
    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

SP can be used to filter data as per given parameters as:

No parameters provided, fetch first 10 default records with title in ascending Order :
EXEC USP_SEL_Contacts
 On providing @SortCol = ‘FirstName’ and @SortOrder=’Asc’, 
 will fetch 10 records sorted by First Name in ascending order:
EXEC USP_SEL_Contacts @SortColumn = ‘FirstName’, @SortOrder=‘Asc’
 On providing @SortCol = ‘LastName’, will fetch 10 records 
 sorted by Last Name in descending order:
EXEC USP_SEL_Contacts @SortColumn = ‘FirstName’, @SortOrder=‘Desc’
 Following query will pull out 20 records in a page having 
 Email Address ‘b@b.cc’ sorted by Last Name in Descending order:
EXEC USP_SEL_Contacts @EmailAddress = ‘b@b.cc’, @PageSize = 20, @PageNo=1, 
     @SortColumn = ‘LastName’ , @SortOrder=‘Desc’

Note: Please note that while publishing blog single quotes ‘ & minus sign – are converted to some unrecognizable characters, if you have error like:

Msg 102, Level 15, State 1, Procedure USP_SEL_Contacts, Line 14
Incorrect syntax near ‘‘’.
Msg 137, Level 15, State 2, Procedure USP_SEL_Contacts, Line 42
Must declare the scalar variable "@SortColumn".
Msg 102, Level 15, State 1, Procedure USP_SEL_Contacts, Line 43
Incorrect syntax near ‘–’.
Msg 102, Level 15, State 1, Procedure USP_SEL_Contacts, Line 45
Incorrect syntax near ‘–’.
Msg 102, Level 15, State 1, Procedure USP_SEL_Contacts, Line 49
Incorrect syntax near ‘‘’.

Please replace those characters by single quotes & minus sign respectively.