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

回答1:

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.

回答2:

Let see if this is what you need:

DECLARE @t table(col1 int, col2 int, col3 int,col4 int);
DECLARE @OrderBy varchar(4) = 'Col3';

insert into @t 
values
 (1,2,3,4),
 (6,3,8,5),
 (7,4,1,7),
 (3,7,0,1),
 (9,8,3,6);

 SELECT 
     CASE @OrderBy
        WHEN 'Col2' THEN Col2
        WHEN 'Col3' THEN Col3
        WHEN 'Col4' THEN Col4
        ELSE Col1 END as OrderCol
    ,* 
 FROM @t
 ORDER BY OrderCol desc;

 SELECT * 
 from @t
 ORDER BY CASE @OrderBy
    WHEN 'Col2' THEN Col2
    WHEN 'Col3' THEN Col3
    WHEN 'Col4' THEN Col4
    ELSE Col1 END desc;