Dynamic ORDER BY with multiple criteria in T-SQL

Introduction

Sometimes you need to allow users to order the results of a query the way they want, and that means one or more columns in the ORDER BY clause that can change at runtime. This article will show you how to easily allow dynamic ORDER BY criteria in a query without the need for dynamic SQL or multiple SELECT statements nested inside of IF logic.

Background 

The product I work on provides a UI where users can choose filter, sort and group by criteria for a list of hundreds of reports. Typically the stored procedures behind the reports are using dynamic SQL so that they can build a string for the ORDER BY clause because we allow the sort to be on a number of fields. Justified or not, I have a strong aversion to dynamic sql so I had to find a way to build the sort with pure T-SQL.

Surprisingly, Google search brought up limited options. In searching, I found three main solutions:

• Dynamic SQL
• Multiple select statements with hard-coded ORDER BYs against temp tables chosen by IF logic
• CASE statements that didn't handle multiple criteria, or just didn't work.

The "A-ha" moment came when I realized that just like a normal ORDER BY, I needed to separate the CASE statements by commas. 

Using the code  

Below I provide a contrived example script. It defines a @FruitOrder variable that would likely be passed into a stored procedure. 

The code builds a temp table, populates data and then provides various ways of sorting the results based on what @FruitOrder is at runtime. 

--DROP TABLE #fruit;

CREATE TABLE #fruit (
    FruitName NVARCHAR(30),
    FruitColor NVARCHAR(30),
    FruitCost NUMERIC(6,2),
    GrowsOn NVARCHAR(30)
);

INSERT INTO #fruit (FruitName, FruitColor, FruitCost, GrowsOn)
    VALUES('Apple', 'Red', .49, 'Tree');
INSERT INTO #fruit (FruitName, FruitColor, FruitCost, GrowsOn)
    VALUES('Grape', 'Purple', 1.39, 'Vine');
INSERT INTO #fruit (FruitName, FruitColor, FruitCost, GrowsOn)
    VALUES('Banana', 'Yellow', .29, 'Tree');
INSERT INTO #fruit (FruitName, FruitColor, FruitCost, GrowsOn)
    VALUES('Banana', 'Green', .19, 'Tree');
INSERT INTO #fruit (FruitName, FruitColor, FruitCost, GrowsOn)
    VALUES('StrawBerry', 'Red', 3.29, 'Bush');
INSERT INTO #fruit (FruitName, FruitColor, FruitCost, GrowsOn)
    VALUES('Cherry', 'Red', 2.69, 'Tree');
INSERT INTO #fruit (FruitName, FruitColor, FruitCost, GrowsOn)
    VALUES('Cherry', 'Red', 1.69, 'Vine');
INSERT INTO #fruit (FruitName, FruitColor, FruitCost, GrowsOn)
    VALUES('Cherry', 'Red', 3.69, 'Bush');    
INSERT INTO #fruit (FruitName, FruitColor, FruitCost, GrowsOn)
    VALUES('Plum', 'Purple', .79, 'Tree');
INSERT INTO #fruit (FruitName, FruitColor, FruitCost, GrowsOn)
    VALUES('BlueBerry', 'Purple', 9.29, 'Bush');

DECLARE @FruitOrder NVARCHAR(30);
--SET @FruitOrder = 'Name';
--SET @FruitOrder = 'NameThenColor';
SET @FruitOrder = 'NameThenCost';
--SET @FruitOrder = 'NameThenGrowsOn';
--SET @FruitOrder = 'ColorThenGrowsOn';
--SET @FruitOrder = 'GrowsOnThenNameThenCost';

SELECT
    FruitName, FruitColor, FruitCost, GrowsOn
FROM 
    #fruit
ORDER BY 
    CASE @FruitOrder
        WHEN 'Name' THEN FruitName END,
    CASE @FruitOrder 
        WHEN 'NameThenColor' THEN FruitName END,
    CASE @FruitOrder
        WHEN 'NameThenColor' THEN FruitColor END,
    CASE @FruitOrder
        WHEN 'NameThenCost' THEN FruitName END,
    CASE @FruitOrder
        WHEN 'NameThenCost' THEN FruitCost END,
    CASE @FruitOrder
        WHEN 'NameThenGrowsOn' THEN FruitName END,
    CASE @FruitOrder
        WHEN 'NameThenGrowsOn' THEN GrowsOn END,
    CASE @FruitOrder
        WHEN 'ColorThenGrowsOn' THEN FruitColor END,
    CASE @FruitOrder
        WHEN 'ColorThenGrowsOn' THEN GrowsOn END,
    CASE @FruitOrder
        WHEN 'GrowsOnThenNameThenCost' THEN GrowsOn END,
    CASE @FruitOrder
        WHEN 'GrowsOnThenNameThenCost' THEN FruitName END,
    CASE @FruitOrder
        WHEN 'GrowsOnThenNameThenCost' THEN FruitCost END,
    FruitCost;

--SELECT * FROM #fruit ORDER BY FruitName, FruitCost;
--SELECT * FROM #fruit ORDER BY FruitName, FruitColor, FruitCost;
/** Causes error defining column more than once, but doing so in CASE statement does not ***/
--SELECT * FROM #fruit ORDER BY FruitName, FruitCost, FruitCost ;
--SELECT * FROM #fruit ORDER BY FruitName, GrowsOn, FruitCost;
--SELECT * FROM #fruit ORDER BY FruitColor, GrowsOn, FruitCost;
--SELECT * FROM #fruit ORDER BY GrowsOn, FruitName, FruitCost; 

After you run the code that creates and populates the temp table, experiment by un-commenting the different values of @FruitOrder. Observe how the CASE statements flow to build the final ORDER BY. You will have one CASE statement for each criteria. For example, to sort on GrowsOn then by FruitName, then by FruitCost, it will require three CASE statements to get the final result.

Finally, [FruitCost] is set at the end, just for fun so that no matter what sort is chosen, the cost will always be sorted on last. 

The final select statements are provided for comparison. It will show that the hard-coded ORDER BYs yield the same results as the dynamic query.  

Points of Interest 

Notice how FruitCost is included in some of the CASE statements and again on its own? This is no problem for the query, but as a literal it is an error to specify it more than once.