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.
Manoj, that is _damn_ clever. Had never thought of using the windowing functions like that. Definitely passing that around. Thanks!
Great Work Manoj !! Keep it up . 🙂
thank u man
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
end
@vasudeo, you are doing the same thing by creating dynamic SQL, which is a workaround and is not recommended. This will not use the optimal plan everytime it will run. The approach shown in my blog post is more dynamic in nature, with no dynamic SQL.
It should return Number of records matches for criteria.
Thank you so much…so precise and concrete solution …you saved my so much time man thanks a bunch
Msg 245, Level 16, State 1, Procedure USP_GET_Contacts_DynSearch_Paging_Sorting, Line 49
Conversion failed when converting the varchar value ‘%’ to data type int.
help me!!!!
@James, you might be using a INT datatype column as varchar column with LIKE ‘%’. Find it out, and fix it.
manoj ..its relay worst and performance cost effective ..down vote