Custom Sort Order - How to not duplicate the 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 query (based on the Fuji should come first and Gala second, etc.). This part also work but it create duplicated code in my query. Because of that, I am pretty sure people will not let me check this in. 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 could multiply the sorting key by either +1 or -1 depending on whether ASC or DESC is 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're on SQL 2008, you could use a 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

It's not as slick 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 comparable value, join on the variety column, and sort on the comparison value.

E.g.

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 do the trick also while being a bit more database-y?

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