[Solved] Order by two columns with usage of CASE WHEN

by


CASE is an expression that returns a single expression/value. You need to write one CASE statement per column:

ORDER BY            
CASE WHEN @cOrderBy = 'USER_FNM_USER_LNM ASC'        THEN USER_LNM END ASC,
CASE WHEN @cOrderBy = 'USER_FNM_USER_LNM DESC'       THEN USER_LNM END DESC,
CASE WHEN @cOrderBy IS NULL                          THEN USER_KEY END ASC,

CASE WHEN @cOrderBy = 'USER_FNM_USER_LNM ASC'        THEN USER_FNM END ASC,
CASE WHEN @cOrderBy = 'USER_FNM_USER_LNM DESC'       THEN USER_FNM END DESC

Update (to reflect updated question)

Since you have ROW_NUM column, you can sort by that:

CASE WHEN @cOrderBy = 'USER_FNM_USER_LNM ASC'        THEN ROW_NUM END ASC,
CASE WHEN @cOrderBy = 'USER_FNM_USER_LNM DESC'       THEN ROW_NUM END DESC,
CASE WHEN @cOrderBy IS NULL

2

solved Order by two columns with usage of CASE WHEN