SQL : CASE Statements in ORDER BY clause....possible?

While digging through some SQL code, we discovered that you can in fact use CASE statements inside the ORDER BY clause. We have found that clients like to use the ORDER BY on various reports, but having the option to dynamically order the display or the results.

The old way of doing this, is to write dynamic SQL, but we know that is not as efficient as can be.
Here is an example of how to go about using CASE statements in the ORDER BY clause.

/**

Create Temp table and fill with sampledata

**/

CREATE TABLE #Company

      (

           CompanyID int

           ,IndustryNumber varchar(20)

           ,RegisteredName varchar(50)

           ,TradingName varchar(50)

      )

/**

Insert the data into the temp table

**/ 

INSERT INTO #Company

SELECT 18,   'GP 00028/2005', 'DESIGN BY SORELLE CC',  'M&V INTERIORS'

INSERT INTO #Company

SELECT 19,   'GP 00029/2005', 'WILLIAM TELL INDUSTRIES(PTY) LTD', 'WILLIAMTELL INDUSTRIES (REUVEN ESTATES)'

INSERT INTO #Company

SELECT 24,   'NW 00034/2005', 'M C GHOOR & SON (PTY)LTD',   'GHOORS'

INSERT INTO #Company

SELECT 28,   'GP 00040/2005', 'LIVANGO (PTY) LTD', 'MEYERS CURTAINS'

INSERT INTO #Company

SELECT 30,   'GP 00043/2005', 'RAY ROWLEY OFFICEFURNITURE (PTY) LTD', 'SCEENCOR'

INSERT INTO #Company

SELECT 34,   'GPJ00048/2005', 'GEORGE SMITH', 'ANBEL ENTERPRISES'

INSERT INTO #Company

SELECT 42,   'MP 00059/2005', 'P.R. UPHOLSTERERERS', 'PIET RETIEF UPHOLSTERS'

INSERT INTO #Company

SELECT 45,   'GP 00063/2005', 'G G INTERIORS DECORATORSCC', 'ROSEMARIEINTERIORS DECORATORS'

/**

Method 1: Declare an order by variable(@OrderBy) in which can pass the desired Order By X field to.

Note: The data type of each ordered byitem needs to be the same type or a casting error occurs - integers can be CAST to varchar but this causesincorrect ordering e.g result: 1, 11, 15, 2 ,23 , 30

**/

DECLARE @OrderBy varchar(100) = 'TradingName' --RegisteredName  -- Other -- TradingName

SELECT c.*

FROM#Company  c     WITH (NOLOCK)

ORDER BY

      CASE

            WHEN @OrderBy = 'TradingName' THEN c.TradingName

           WHEN @OrderBy ='RegisteredName' THENc.RegisteredName

      ELSE

            c.IndustryNumber

      END

/**

Method 2: By creating separate casestatements no casting errors occur and can cater for various ordering.

If working with reporting services cancreate a dataset on the report or a stored procedure that passes what each Order By ID represents for theusers to select.

e.g

CREATE PROCEDURE prReport_OrderByOptions

AS

SELECT

     1                                   AS OrderByID

     ,'CompanyID'                        AS OrderByOption

    

UNION

SELECT

     2                                   AS OrderByID

      ,'RegisteredName'                  AS OrderByOption 

     

UNION

SELECT

     3                                   AS OrderByID

     ,'IndustryNumber'                  AS OrderByOption

**/

DECLARE @OrderByID int

SET @OrderByID = 1

SELECT

      *

FROM#Company   c     WITH (NOLOCK)

ORDER BY

      CASE WHEN @OrderByID = 1 THEN c.CompanyID  END,

      CASE WHEN @OrderByID = 2 THEN c.RegisteredName   END,

      CASE WHEN @OrderByID = 3 THEN c.IndustryNumber END

     

DROPTABLE #Company     

Getting away from not hard coding at all, is a tough challenge, especially related to SQL. The next point to think of is ORDER BY multiple columns...?
That's all for now. Please feel free to comment or give any feedback on the post.