Sometimes when the user wants to sort results in a view, it makes sense to do all of that sorting back in the database. The C# controller passes what column to sort by and what direction to sort by to the SQL Sever stored procedure, when it executes the command with ADO.NET.

Conditional sorting is easy to do in modern versions of SQL Server.

However, I have seen these sorts performed by PARTITION BY or ROW_NUMBER and other windowing functions. Often the direction of the final sort was determined by the row number. Usually it makes more sense to just use ORDER BY in various CASE statements.

Here is a simplified version of a conditional sort being performed using ROW_NUMBER():

USE [MyDb]
GO

ALTER PROCEDURE [Users].[GetSalesPeople]
	@SortByColumn VARCHAR(100),
	@SortDirection VARCHAR(4)
AS
BEGIN
	--Verify Sort By Column
	IF @SortByColumn IS NULL
		BEGIN
			--Set Default Sort By Column
			SET @SortByColumn = 'FullName'
		END
	--Verify Sort Direction
	IF @SortDirection IS NULL
		BEGIN
			--Set Default Sort Direction
			SET @SortDirection = 'ASC'
		END
...
--Get Salesperson Details
SELECT
	t.UserId,
	t.FirstName,
	t.LastName,
	t.EmailAddress,
	t.EmployeeNumber,
	t.RoleTypeId,
	t.StartDate,
	t.EndDate,
	t.ManagerId
FROM (SELECT
		t.UserId,
		u.FirstName,
		u.LastName,
		u.EmailAddress,
		u.EmployeeNumber,
		r.RoleTypeId,
		u.StartDate,
		u.EndDate,
		u.ManagerId
		CASE
			WHEN @SortDirection = 'ASC' AND @SortByColumn = 'ID' THEN ROW_NUMBER() OVER (ORDER BY u.UserId)
			WHEN @SortDirection = 'DESC' AND @SortByColumn = 'ID' THEN ROW_NUMBER() OVER (ORDER BY u.UserId DESC)
			WHEN @SortDirection = 'ASC' AND @SortByColumn = 'FirstName' THEN ROW_NUMBER() OVER (ORDER BY u.FirstName)
			WHEN @SortDirection = 'DESC' AND @SortByColumn = 'FirstName' THEN ROW_NUMBER() OVER (ORDER BY u.FirstName DESC)
		...
		END AS RowNumber
	FROM Users.[User] u
	JOIN Users.[Role] r ON
		u.UserId = r.UserId
	...
	ORDER BY 
		t.RowNumber

The ‘sort by column’ feature is implemented by the stored procedure receiving what column the user wants to sort by. Then ROW_NUMBER() OVER to sort the column.

The above works on the same principle all this:

SELECT *
FROM
    TableName
WHERE
ORDER BY 
    CASE @OrderByColumn
    WHEN 1 THEN EmployeeName DESC
    WHEN 2 THEN EmployeeName ASC
    END;

Note: the above does not work. You can not apply ‘DESC’ and ‘ASC’ ORDER BY this way.

The case statement must be first wrapped in parenthesis…

SELECT *
FROM
    TableName
WHERE
ORDER BY 
    (CASE @OrderByColumn
    WHEN 1 THEN EmployeeName
    END) DESC 
,   (CASE @OrderByColumn
    WHEN 2 THEN EmployeeName
    END) ASC

After all that, I realized, why use ROW_NUMBER() number values at all. A simpler solution is to just use pure ORDER BY with CASE statements.

SELECT SalesPersonNumber, SalesPersonName, ContractNumber, SUM(TotalSales) As TotalSales FROM @ProcessedSalesByEmployee 
    GROUP BY SalespersonNumber, SalesPersonName, ContractNumber  
    ORDER BY
    (CASE WHEN @SortByColumn = 'SalesPersonNumber' AND @SortDirection = 'ASC' THEN SalesPersonNumber END) ASC,
    (CASE WHEN @SortByColumn = 'SalesPersonNumber' AND @SortDirection = 'DESC' THEN SalesPersonNumber END) DESC,
    (CASE WHEN @SortByColumn = 'SalesPersonName' AND @SortDirection = 'ASC' THEN SalesPersonName END) ASC,
    (CASE WHEN @SortByColumn = 'SalesPersonName' AND @SortDirection = 'DESC' THEN SalesPersonName END) DESC,
    (CASE WHEN @SortByColumn = 'ContractNumber' AND @SortDirection = 'ASC' THEN ContractNumber END) ASC,
    (CASE WHEN @SortByColumn = 'ContractNumber' AND @SortDirection = 'DESC' THEN ContractNumber END) DESC,
    (CASE WHEN @SortByColumn = 'TotalHours' AND @SortDirection = 'ASC' THEN SUM(TotalHours) END) ASC,
    (CASE WHEN @SortByColumn = 'TotalHours' AND @SortDirection = 'DESC' THEN SUM(TotalHours) END) DESC,

Note: TotalHours which has an aggregate function applied, is not listed in GROUP BY. All aggregate functions, like SUM(), require a GROUP BY. We do not include SUM() in the GROUP BY, the GROUP BY tells us how to organize our count.

Also, notice that ASC and DESC re not included inside the CASE statement. The entire CASE statement transaction is inside ( ) and ends with END. ASC and DESC must be outside the parenthesis.