SQL Server dynamic Order By in query, different data types

Question

In SQL Server 2000

I have a query like

SELECT DISTINCT A.COLUMN1, B.COLUMN2 FROM TABLEA A, TABLEB B WHERE 
A.KEY_ID = B.FK_ID

ORDER BY CASE @ORDER_NAME
         WHEN 'COL1' THEN COLUMN1
         WHEN 'COL2' THEN COLUMN2
         ELSE
         COLUMN2
         END ASC

Here A.COLUMN1 is varchar(50) and B.COLUMN2 is datetime. This query works perfectly when value of @ORDER_NAME is 'COL2' i.e. when order by is of type datetime but when I used 'COL1' it gives error 'Syntax error converting datetime from character string.'

I think that this is because SQL Server is trying to convert all the columns to datetime type. But I can't find an alternative syntax to dynamically sort the columns. EXEC is out of question due to performance issues

I need to mention that I am trying to avoid branching otherwise the above can be done by ane IF ELSE clause also.

Answer 1

Version without any data type conversion issues:

SELECT  DISTINCT 
        A.COLUMN1, 
        B.COLUMN2 
FROM    TABLEA A, 
        TABLEB B 
WHERE   A.KEY_ID = B.FK_ID
ORDER BY 
        CASE @ORDER_NAME WHEN 'COL1' THEN COLUMN1 ELSE NULL END,
        CASE @ORDER_NAME WHEN 'COL2' THEN COLUMN2 ELSE NULL END