SQLSERVER - 'Conversion failed' error with ORDER BY CASE statement

In SqlServer, single CASE statement should require that all branches should have same or compatible datatypes. We should not mix all different data types into one CASE statement.

If we mix different data-types into once CASE Statement then conversion will be happen based as data type priority. See below the priority list from higher to lower

i. datetime
ii. decimal
iii. int
iv. bit
v.  nvarchar
vi. varchar




Solution : divide once CASE statement into Multiple CASE statements. So each CASE statement holds only one data type values.

--creating table 
CREATE TABLE USERTable (
    NAME VARCHAR(10)
    ,DOB DATETIME
    ,ADDRESS varchar(50)
    )
--inserting rows   
INSERT INTO USERTable (Name,DOB,ADDRESS) values ('Name1','2/2/2012','add1')
INSERT INTO USERTable (Name,DOB,ADDRESS) values ('Name2','3/2/2012','add2')
INSERT INTO USERTable (Name,DOB,ADDRESS) values ('Name3','4/2/2012','add3')
INSERT INTO USERTable (Name,DOB,ADDRESS) values ('Name4','5/2/2012','add4')
INSERT INTO USERTable (Name,DOB,ADDRESS) values ('Name15','6/2/2012','add5')
INSERT INTO USERTable (Name,DOB,ADDRESS) values ('Name6','2/21/2012','add6')
--select rows by order
DECLARE @SortBy VARCHAR(10) = 'Name'

SELECT *
FROM USERTable
ORDER BY CASE @SortBy
        WHEN 'Name'
            THEN NAME
        WHEN 'DOB'
            THEN DOB
        WHEN 'Add'
            THEN ADDRESS
        END DESC

Resolution :

DECLARE @SortBy VARCHAR(10) = 'Name'

SELECT *
FROM USERTable
ORDER BY CASE @SortBy
        WHEN 'Name'
            THEN NAME
        WHEN 'Add'
            THEN ADDRESS
        END DESC
    ,CASE @SortBy
        WHEN 'DOB'
            THEN DOB
        END DESC