Custom Sort Order - How not to duplicate Case statement

Question

I have the following db and query. The query takes two parameters: the sort column and the direction. However, I have to add custom sorting to the request (based on Fuji should go first, and Gala second, etc.). This part also works, but it creates duplicate code in my request. Because of this, I am pretty sure that people will not let me verify this. So my question is: is there a way to not duplicate the CASE statement?

CREATE TABLE Fruits ( [type] nvarchar(250), [variety] nvarchar(250), [price] money ) GO INSERT INTO Fruits VALUES ('Apple', 'Gala', 2.79) INSERT INTO Fruits VALUES ('Apple', 'Fuji', 0.24) INSERT INTO Fruits VALUES ('Apple', 'Limbertwig', 2.87) INSERT INTO Fruits VALUES ('Orange', 'Valencia', 3.59) INSERT INTO Fruits VALUES ('Pear', 'Bradford', 6.05) DECLARE @sortColumnName nvarchar(MAX) = 'Variety' DECLARE @sortDirection nvarchar(MAX) = 'ASC' SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @sortColumnName = 'Variety' AND @sortDirection = 'ASC' THEN CASE f.Variety WHEN 'Fuji' THEN 1 WHEN 'Gala' THEN 2 ELSE 3 END END ASC, CASE WHEN @sortColumnName = 'Variety' AND @sortDirection = 'DESC' THEN CASE f.Variety WHEN 'Fuji' THEN 1 WHEN 'Gala' THEN 2 ELSE 3 END END DESC), * FROM Fruits f 

Answer 1

You can multiply the sort key by +1 or -1 depending on whether ASC or DESC is being requested:

 SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @sortColumnName = 'Variety' THEN (CASE f.Variety WHEN 'Fuji' THEN 1 WHEN 'Gala' THEN 2 ELSE 3 END) END * (CASE WHEN @sortDirection = 'ASC' THEN 1 ELSE -1 END)), * FROM Fruits f 

Answer 2

Since you are using SQL 2008, you can use CTE:

 ;WITH CTE AS ( SELECT CASE WHEN @sortColumnName = 'Variety' THEN CASE f.Variety WHEN 'Fuji' THEN 1 WHEN 'Gala' THEN 2 ELSE 3 END END AS sort_column, * FROM Fruits F ) SELECT ROW_NUMBER() OVER ( ORDER BY CASE WHEN @sortDirection = 'DESC' THEN sort_column ELSE 0 END DESC, CASE WHEN @sortDirection = 'ASC' THEN sort_column ELSE 0 END ASC), type, variety, price FROM CTE 

This is not as smooth as the * -1 solution for this particular problem, but it can be adapted for other situations where you want to avoid code duplication.

Answer 3

Why not have another table for a comparable value, join the sort column and sort the comparison value.

eg.

 INSERT INTO FruitSort VALUES ('Gala', 2) INSERT INTO FruitSort VALUES ('Fuji', 1) SELECT ROW_NUMBER() OVER (ORDER BY FruitSort.sortvalue) FROM Fruits f JOIN FruitSort ON FruitSort.variety == Fruits.variety 

Wouldn't that be a trick, at least a little larger than database-y?

I'm not very practiced, so the syntax is probably pretty broken. I am completely dissatisfied with the concept of CASE statements in SQL.