Advertisements
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.

Advertisements
  1. James1008
    December 9, 2015 at 7:06 pm

    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!!!!

    • December 9, 2015 at 7:17 pm

      @James, you might be using a INT datatype column as varchar column with LIKE ‘%’. Find it out, and fix it.

  2. adnan khan
    August 7, 2013 at 4:11 am

    Thank you so much…so precise and concrete solution …you saved my so much time man thanks a bunch

  3. December 7, 2012 at 5:30 pm

    It should return Number of records matches for criteria.

  4. vasudeo vaka
    May 10, 2012 at 11:29 am

    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

    • May 10, 2012 at 1:56 pm

      @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.

  5. ahmad
    April 11, 2012 at 6:18 am

    thank u man

  6. Surinder
    December 31, 2011 at 3:55 am

    Great Work Manoj !! Keep it up . 🙂

  7. December 30, 2011 at 4:37 pm

    Manoj, that is _damn_ clever. Had never thought of using the windowing functions like that. Definitely passing that around. Thanks!

  1. June 26, 2015 at 3:31 am
  2. September 15, 2012 at 2:12 pm

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

%d bloggers like this: