ORDER BY AND CASE IN SQL SERVER

case, sql, sql-order-by, sql-server

Solution

`CASE` is an expression and has to produce a result of a single well defined type. So as long as the types of all columns are compatible, they can all be placed into a single `CASE` expression.

If that's not the case then you need to split it up and use multiple expressions. Say that `Col1` and `Col3` have compatible types (whether the same or you're happy for one to convert to the other) and that `Col2` and `Col4` have incompatible types (both between themselves and with `Col1` and `Col3`), then we need three expressions:

ORDER BY 
    CASE @OrderBy
        WHEN 'Col1' THEN Col1
        WHEN 'Col3' THEN Col3
    END
    , CASE @OrderBy WHEN 'Col2' THEN Col2 END
    , CASE @OrderBy WHEN 'Col4' THEN Col4 END
    , Col1

(I've also include a final expression of `Col1` so that your "fallback" sort still occurs)

For each of the `CASE` expressions above, if no match occurs then the expression returns `NULL` - and all `NULL`s sort together, so that that entire `CASE` expression then has no overall effect on the sorting.

From `CASE`:

Return Types

Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression.

Problem

I need to have an order by functionality inside a stored procedure. A value is posted to a webservice and based on that value I have to order the results in a certain way i.e. When ColName is posted order by ColName When ColName2 is posted order by ColName2 I was looking into using Case but I am getting an error: ``` Incorrect syntax near '@version' ORDER BY CASE WHEN @OrderBy ='Seller (code)' THEN A_SNO WHEN @OrderBy ='Lot' THEN A_LOTNO WHEN @OrderBy ='Ring Type' THEN RN_NUM WHEN @OrderBy ='Aim Error Code' THEN AimRejectionCode ELSE A_SNO END DECLARE @version varchar(50) SET @version = (SELECT DBVERSION FROM MSYSCFG) PRINT 'New Version = ' + @version ``` Sorry I'm new to this and using sql server 2008. Any help appreciated UPDATE: Provided additional code. When I leave out the last 3 lines I get an error of ``` Incorrect synatx near END ``` UPDATE2: I've changed the ORDER BY TO the following: ``` ORDER BY CASE @OrderBy WHEN @OrderBy = 'Seller (code)' THEN A_SNO WHEN @OrderBy = 'Lot' THEN A_LOTNO WHEN @OrderBy = 'Aim Error Code' THEN AimRejectionCode END , CASE @OrderBy WHEN 'Ring Type' THEN RingTypeFlag END , A_SNO ``` The first three are varchar and the other is of type int. This is giving me red lines under all three '=' with an error description of: `'incorrect syntax near '='` and a red line under ORDER BY which gives an error description of: ``` 'A constant expression was encountered in the ORDER BY list, position 3' ``` Note when I remove the final `, A_SNO` The Order By error is gone but I am still receiving the = syntax error

Original source