Stored Procedure having Sorting, Paging and Filtering

Posted: January 7, 2013 in CodeProject, SQL
Tags: , , , , , , ,

In day to day work we often need data to 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 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.

www.HyperSmash.com

About these ads
Comments
  1. Hello , you are awsome writer, i love your site
    Fight betting

  2. Amily Rogers says:

    Works like charm, thanks.

  3. Charlie says:

    That works great but in some cases execution plan shows sp call is not using some indexes. Don’t know exact cases but happens some times. What might be the problem.

  4. Yahoo Answers are loved by Google and many of the Yahoo Answers are sitting on high ranking especially for some highly competitive keywords. You can’t even imagine what effect can Yahoo Answers provide to the traffic of your site. Don’t wait and learn more about this at http://yanswertraffic.com/

  5. samsung smartphone says:

    Great – I should definitely say I am impressed with your web site. I had no trouble navigating via all tabs and related info. The site ended up being truly easy to access. Superb occupation..

  6. lista de emails says:

    thanks for the nice post.

  7. online micro jobs says:

    I’m truly enjoying the style and layout of one’s site. It is a really easy on the eyes which makes it a lot much more enjoyable for me to come here and visit much more often. Did you hire out a designer to create your theme? Fantastic function!

  8. 手機殼 says:

    手機殼

    This article is obviously written for people like me with curious minds. I agree with several of the points written within this article. Thank you.

  9. wayfair code says:

    hi Nice Blog

  10. cassio neguinho says:

    i didn’t like this post very much but all of your other posts are very good.

  11. regan says:

    Thanks for finally talking about >Stored Procedure having Sorting, Paging
    and Filtering | Connoisseur <Liked it!

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s