SQL statement with CASE WHEN in ORDER BY causes type convert error

Executing the following code triggers an error:

System.Data.SqlClient.SqlException: 'Cannot convert a char value to money. The char value has incorrect syntax.

All was fine until I have added the second parameter used for sorting purposes. The code is simplification for clarity.

query as String = "
SELECT a, b, c
FROM DataTable
WHERE c = @PARAM
ORDER BY
    CASE @SORTCOLUMN
        WHEN 1 THEN a
        WHEN 2 THEN b
        WHEN 3 THEN c

    END"

Dim param As String = "myprarameter"
Dim sortcolumn as Integer = 1

result = connection.Query(Of MyType)(query, New With {Key .PARAM = param, Key .SORTCOLUMN = sortcolumn})

UPDATE:

After hours spent on testing I have narrowed it down to purely SQL issue, not Dapper nor .NET Framework.

Here are my findings: All works fine until all columns used within CASE WHEN are of the same type (or types and even values which can be easily converted into each other). Once types of columns are different and values cannot be converted, it returns type conversion error. Seems it is trying to convert type of the column selected by CASE WHEN to the type of the first WHENcolumn.

Herewith two examples. I have removed variables for simplicity.

This works:

CREATE TABLE #TestTable1
(col1 money, col2 money)

INSERT INTO #TestTable1
(col1, col2)
VALUES
(1, 30),
(2, 20),
(3, 10)

SELECT col1, col2
FROM  #TestTable1
ORDER BY
    CASE 2
        WHEN 1 THEN col1
        WHEN 2 THEN col2
    END

DROP TABLE #TestTable1

But this does NOT work. Returns:

Cannot convert a char value to money. The char value has incorrect syntax.

CREATE TABLE #TestTable2
(col1 money, col2 varchar(20))

INSERT INTO #TestTable2
(col1, col2)
VALUES
(1, 'cc'),
(2, 'bb'),
(3, 'aa')

SELECT col1, col2
FROM  #TestTable2
ORDER BY
    CASE 2
        WHEN 1 THEN col1
        WHEN 2 THEN col2
    END

DROP TABLE #TestTable2

I am using Azure SQL with compatibility level 150. I have updated Title and Tags accordingly.

UPDATE 2: I am trying to add a complication to @forpas solution in form of a second parameter which will tell the order. I have used CASE within CASE but this returns a number of syntax errors. The ORDER part only. The rest has not changed.

ORDER BY
CASE @SORTORDER
WHEN 'a' THEN
   (CASE @SORTCOLUMN
        WHEN 1 THEN col1
        WHEN 2 THEN col2
    END,
    CASE @SORTCOLUMN
        WHEN 3 THEN col3
        WHEN 4 THEN col4
    END) ASC
WHEN 'd' THEN
    (CASE @SORTCOLUMN
        WHEN 1 THEN col1
        WHEN 2 THEN col2
    END,
    CASE @SORTCOLUMN
        WHEN 3 THEN col3
        WHEN 4 THEN col4
    END) DESC
END

Solution

One solution is to split the CASE statement to as many CASE statements needed so each one contains columns with the same or similar convertable data types:

CREATE TABLE #TestTable2
(col1 money, col2 decimal(18, 2), col3 varchar(20))

INSERT INTO #TestTable2
(col1, col2, col3)
VALUES
(1, 5.5, 'cc'),
(2, 1.8, 'bb'),
(3, 3.3, 'aa');

DECLARE @SORTCOLUMN INT = 1; -- 1 or 2 or 3

SELECT col1, col2, col3
FROM  #TestTable2
ORDER BY
    CASE @SORTCOLUMN 
      WHEN 1 THEN col1
      WHEN 2 THEN col2 
    END,
    CASE @SORTCOLUMN 
      WHEN 3 THEN col3 
    END

DROP TABLE #TestTable2

See the demo.