SQL Server dynamic order There are different data types in a query

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 the date and time. This query works fine when @ORDER_NAME is "COL2", that is, when order by is of type datetime, but when I used "COL1", it gives the error "Syntax error converting date and time from character string".

I think this is because SQL Server is trying to convert all columns to datetime type. But I cannot find an alternative syntax for dynamically sorting columns. EXEC is out of the question due to performance issues.

I need to mention that I try to avoid branching, otherwise the above can also be done with the IF ELSE clause.

Answer 1

- :

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

Answer 2

:

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 3

- :

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

:

, , varchar , , , , , xquery, .

Answer 4

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

, , :

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