user2363025 user2363025 - 4 months ago 10
SQL Question

ORDER BY AND CASE IN SQL SERVER

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

Answer

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 NULLs 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.

Comments