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

This should work:

IF @ORDER_NAME = 'COL1'
BEGIN
    SELECT DISTINCT A.COLUMN1, B.COLUMN2 FROM TABLEA A, TABLEB B WHERE A.KEY_ID = B.FK_ID
    ORDER BY COLUMN1 ASC
END

ELSE
BEGIN
    SELECT DISTINCT A.COLUMN1, B.COLUMN2 FROM TABLEA A, TABLEB B WHERE A.KEY_ID = B.FK_ID
    ORDER BY COLUMN2 ASC
END

Answer 2

You could do something like:

SELECT DISTINCT A.COLUMN1, B.COLUMN2, 
    CASE @ORDER_NAME
     WHEN 'COL1' THEN COLUMN1
     ELSE CONVERT(COLUMN2,  --convert to nvarchar in a way that orders properly, cant remember off the top of my head 
    END SortCol
FROM TABLEA A, TABLEB B
WHERE A.KEY_ID = B.FK_ID
ORDER BY SortCol ASC

update:

Actually you could probably convert the date to an varchar in you query and it would work, I've just done it like the above before, when the sort columns where coming from an xquery expression so posted it like that.

Answer 3

convert them to all to varchar:

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 CONVERT(varchar(nn),COLUMN1)
         WHEN 'COL2' THEN CONVERT(varchar(23),COLUMN2,121)
         ELSE
         CONVERT(varchar(23),COLUMN2,121)
         END ASC

you could then even sort by multiple columns then, which is good when you have many similar values in the sort by column:

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 CONVERT(varchar(nn),COLUMN1)+CONVERT(varchar(23),COLUMN2,121)
         WHEN 'COL2' THEN CONVERT(varchar(23),COLUMN2,121)+RIGHT(REPLICATE('0',nn)+CONVERT(varchar(nn),COLUMN1),nn)
         ELSE CONVERT(varchar(23),COLUMN2,121)+RIGHT(REPLICATE('0',nn)+CONVERT(varchar(nn),COLUMN1),nn)
         END ASC

Answer 4

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