What really, really kills me is when I was in the situation of needing to order SQL results differently based on a parameter given. Previously I thought I had two options in this scenario:

1. Write some dynamic SQL string and execute that with the ORDER BY statement injected.

2. Dump the results into a table variable or temp table and then do an if statement for each different order by.

This seems like a lot of work when really all I want to do is change the ordering of the results. I questioned many times, why can’t I use a CASE statement in the ORDER BY clause. Then I tried it and to my delight it was worked great for the most part, with a couple caveats.

Check out this example:

We create a temp table for this example called employees and then decided how to order the selected results based on a pseudo variable (could be report input..etc).

— Create example table

CREATE TABLE #Emps (EmpNo VARCHAR(3) PRIMARY KEY NOT NULL, FirstName VARCHAR(100), LastName VARCHAR(100))

INSERT INTO #Emps VALUES(1, ‘Travis’, ‘Gosselin’)

INSERT INTO #Emps VALUES(2, ‘John’, ‘Doe’)

INSERT INTO #Emps VALUES(3, ‘Jane’, ‘Watson’)

— ColumnOrder: 0 – EmpNo

— ColumnOrder: 1 – FirstName

— ColumnOrder: 2 – LastName

DECLARE @ColumnOrder INT

— Adjust this value to one of the values above

SET @ColumnOrder = 1

— SELECT with dynamic Order by

SELECT EmpNo, FirstName, LastName

FROM #Emps

ORDER BY

    CASE

        WHEN @ColumnOrder = 1 THEN FirstName

        ELSE LastName

    END

However, you will notice that I have not included when @ColumnOrder = 0. In the above syntax you can only use one data type. EmpNo is an INT and the other columns are VARCHAR. This would cause a SQL run time error. To avoid this, you can simply use different case statements when you need to order by different data types like below:

— SELECT with dynamic Order by

SELECT EmpNo, FirstName, LastName

FROM #Emps

ORDER BY

    CASE WHEN @ColumnOrder = 0 THEN EmpNo END,

    CASE WHEN @ColumnOrder = 1 THEN FirstName END,

    CASE WHEN @ColumnOrder = 2 THEN LastName END

And that is it. Simple and easy, just as you would expect it to be!