Click here to Skip to main content
13,139,569 members (55,031 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

55.6K views
21 bookmarked
Posted 9 May 2013

Stored Procedure with Sorting, Paging, and Filtering

, 9 May 2013
Rate this:
Please Sign up or sign in to vote.
Stored Procedure with sorting, paging, and filtering.

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 = ( @lPageNbr1 ) * @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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

PRANAV SINGH
India India
No Biography provided

You may also be interested in...

Comments and Discussions

 
AnswerMy vote of 4 Pin
.NET DJ17-Jul-15 5:23
professional.NET DJ17-Jul-15 5:23 
GeneralI agree, nice procedure! Pin
jfoster841-May-15 20:46
memberjfoster841-May-15 20:46 
GeneralRe: I agree, nice procedure! Pin
jfoster841-May-15 21:30
memberjfoster841-May-15 21:30 
GeneralMy vote of 5 Pin
MuhammadUSman15-Mar-15 21:45
memberMuhammadUSman15-Mar-15 21:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170915.1 | Last Updated 9 May 2013
Article Copyright 2013 by PRANAV SINGH
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid