Today one of my friend ask me, can we use Order by clause dynamically ? I said yes, but there are few limitations while using case in order by clause see the below example.

This script will return Error:

DECLARE @TOP INT = 10, @FLD VARCHAR(20)='AGENT_ID'

SELECT
    TOP(@TOP)
    *
FROM
(
    SELECT 1 AS AGENT_ID, 'RAM' AS AGENT_NAME
    UNION ALL
    SELECT 2, 'SHYAM'
    UNION ALL
    SELECT 3, 'HARI'
)A
ORDER BY
   CASE WHEN @FLD ='AGENT_ID' THEN AGENT_ID
   WHEN @FLD = 'AGENT_NAME' THEN AGENT_NAME END
DESC

/* 
    Msg 245, Level 16, State 1, Line 7
    Conversion failed when converting the varchar value 'RAM' to data type int.
*/

This Will not return Error:

DECLARE @TOP INT = 10, @FLD VARCHAR(20)='AGENT_ID'

SELECT
    TOP(@TOP)
    *
FROM
(
    SELECT 1 AS AGENT_ID, 'RAM' AS AGENT_NAME
    UNION ALL
    SELECT 2, 'SHYAM'
    UNION ALL
    SELECT 3, 'HARI'
)A
ORDER BY
CASE WHEN @FLD ='AGENT_ID' THEN AGENT_ID END DESC,
CASE WHEN @FLD = 'AGENT_NAME' THEN AGENT_NAME END DESC

The Problem or limitation is Order by clause wants same data type in Each Case statement so we have to use multiple case in each data type group fields.