7

Creating Stored Procedure with Dynamic Search, Paging and Sorting | SQL with Man...

 3 years ago
source link: https://sqlwithmanoj.com/2011/12/30/creating-stored-procedure-with-dynamic-search-paging-and-sorting/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Home > Stored Procedures > Creating Stored Procedure with Dynamic Search, Paging and Sorting

Creating Stored Procedure with Dynamic Search, Paging and Sorting

In my last two previous post we learned to apply [Dynamic Search Capability] and [Pagination] in an SP.

Here in this post we will see how can we apply Sorting at different columns of the returned record set from a Stored Procedure.

In webpage’s data grids when you click on the column header the records are sorted according to the selected column. When you again click that column the ordering reverses for that column. So to implement this functionality we will be passing an extra parameter to the SP which will contain the Column name and sort order, DESC or ASC.

Let’s check this with a sample code using [Person].[Contact] Table of [AdventureWorks] Database:

USE [AdventureWorks]
GO
CREATE PROCEDURE USP_GET_Contacts_DynSearch_Paging_Sorting
(
-- 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
@PageNbr            INT = 1,
@PageSize           INT = 10,
-- Sort Details
@SortCol            NVARCHAR(20) = ''
)
AS
BEGIN
DECLARE
@lContactID         INT,
@lFirstName         NVARCHAR(50),
@lLastName          NVARCHAR(50),
@lEmailAddress      NVARCHAR(50),
@lEmailPromotion    INT,
@lPhone             NVARCHAR(25)
DECLARE
@lPageNbr   INT,
@lPageSize  INT,
@lSortCol   NVARCHAR(20),
@lFirstRec  INT,
@lLastRec   INT,
@lTotalRows INT
SET @lContactID         = LTRIM(RTRIM(@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 @lSortCol   = LTRIM(RTRIM(@SortCol))
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_Asc' THEN ContactID
END ASC,
CASE WHEN @lSortCol = 'ContactID_Desc' THEN ContactID
END DESC,
CASE WHEN @lSortCol = 'Title_Asc' THEN Title
END ASC,
CASE WHEN @lSortCol = 'Title_Desc' THEN Title
END DESC,
CASE WHEN @lSortCol = 'FirstName_Asc' THEN FirstName
END ASC,
CASE WHEN @lSortCol = 'FirstName_Desc' THEN FirstName
END DESC,
CASE WHEN @lSortCol = 'MiddleName_Asc' THEN MiddleName
END ASC,
CASE WHEN @lSortCol = 'MiddleName_Desc' THEN MiddleName
END DESC,
CASE WHEN @lSortCol = 'LastName_Asc' THEN LastName
END ASC,
CASE WHEN @lSortCol = 'LastName_Desc' THEN LastName
END DESC,
CASE WHEN @lSortCol = 'Suffix_Asc' THEN Suffix
END ASC,
CASE WHEN @lSortCol = 'Suffix_Desc' THEN Suffix
END DESC,
CASE WHEN @lSortCol = 'EmailAddress_Asc' THEN EmailAddress
END ASC,
CASE WHEN @lSortCol = 'EmailAddress_Desc' THEN EmailAddress
END DESC,
CASE WHEN @lSortCol = 'EmailPromotion_Asc' THEN EmailPromotion
END ASC,
CASE WHEN @lSortCol = 'EmailPromotion_Desc' THEN EmailPromotion
END DESC,
CASE WHEN @lSortCol = 'Phone_Asc' THEN Phone
END ASC,
CASE WHEN @lSortCol = 'Phone_Desc' THEN Phone
END DESC
) 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

OK, now let’s test this SP:

-- No parameters provided, fetch first 10 default records:
EXEC USP_GET_Contacts_DynSearch_Paging_Sorting
-- On providing @SortCol = 'FirstName_Desc', will fetch 20 records sorted by FirstName:
EXEC USP_GET_Contacts_DynSearch_Paging_Sorting @SortCol = 'FirstName_Asc'
-- On providing @SortCol = 'LastName_Asc', will fetch 20 records sorted by LastName in descending order:
EXEC USP_GET_Contacts_DynSearch_Paging_Sorting @SortCol = 'LastName_Desc'
-- Following query will pull out 20 records in a page having FirstName LIKE 'Sam' sorted by LastName in Descending order:
EXEC USP_GET_Contacts_DynSearch_Paging_Sorting @FirstName = 'Sam', @PageSize = 20, @SortCol = 'LastName_Desc'

Appreciate your valuable comments if we can do this in a different or/and better way.

Related

  1. ALTER procedure [dbo].[SortingandPaging]
    (
    @pagedata int,
    @pagenumbers int,
    @SortExp varchar(50),
    @SortDirec varchar(5),
    @EmpName varchar(50),
    @EmpEmail varchar(50),
    @EmpCompany varchar(50)
    )
    as begin
    DECLARE
    @FirstRow INT,
    @LastRow INT,
    @Query varchar(max)

    SELECT @FirstRow = ( @pagenumbers – 1) * @pagedata + 1,
    @LastRow = (@pagenumbers – 1) * @pagedata + @pagedata ;

    — This is use to search, pagging and sortting of the table
    SET @Query = ‘
    SELECT top ‘+Convert(varchar(3), @LastRow)+’ *
    FROM (SELECT ROW_NUMBER() OVER(ORDER BY ‘+@SortExp+’ ‘+@SortDirec+’ ) AS
    rownum,EmpId, EmpName, EmpAddress,EmpEmail,EmpCompany FROM EmpDetail ) AS Salaries1
    WHERE EmpName like ”%’+ @EmpName +’%” and EmpEmail like ”%’+ @EmpEmail +’%” and EmpCompany like ”%’ + @EmpCompany +’%” and rownum >= ‘+Convert(varchar(3), @FirstRow)
    EXEC (@Query)

    — this is use to get the total number of rows
    SELECT * from EmpDetail

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK