Friday, October 21, 2011

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.