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
Incorrect synatx near END
WHEN @OrderBy = 'Seller (code)' THEN A_SNO
WHEN @OrderBy = 'Lot' THEN A_LOTNO
WHEN @OrderBy = 'Aim Error Code' THEN AimRejectionCode
, CASE @OrderBy WHEN 'Ring Type' THEN RingTypeFlag
'incorrect syntax near '='
'A constant expression was encountered in the ORDER BY list, position 3'
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
If that's not the case then you need to split it up and use multiple expressions. Say that
Col3 have compatible types (whether the same or you're happy for one to convert to the other) and that
Col4 have incompatible types (both between themselves and with
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.
Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression.