Click here to Skip to main content
Click here to Skip to main content

Dynamic ORDER BY with multiple criteria in T-SQL

, 28 May 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
How to use a CASE statement in ORDER BY to avoid dynamic 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. 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Bob Crowley
Software Developer Tyler Technologies
United States United States
Software developer mainly in .Net and T-SQL with ever more experience in HTML5 and javascript.
Follow on   Twitter   Google+

Comments and Discussions

 
QuestionThanks you PinmemberTherrien Stephane8-Dec-13 16:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.141015.1 | Last Updated 28 May 2013
Article Copyright 2013 by Bob Crowley
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid