SQL with Manoj

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.