Why CASE statement doesn't work without nesting?

Question

I have a requirement to sort the results dynamically based on the column selection. I know that only first query below works and 2nd one fails. But am unable to understand why it fails, as per my understanding based on @SortDirection value it should give ASC or DESC, which will get appended to the earlier CASE which is returning column name. Can you please explain why it fails?

create table EmpData
(
EmpID int identity,
FName varchar(20),
LName varchar(20),
Email varchar(30),
City varchar(20),
DOB date
)
GO
INSERT INTO EmpData values ('Mark','Smith','Mark.s@email.com','London','02-Jun-1980')
INSERT INTO EmpData values ('Mark','Marsh','Mark.m@email.com','Paris','02-Jul-1981')
INSERT INTO EmpData values ('Steve','Elgar','Steve.e@email.com','Canada','12-Jun-1980')
INSERT INTO EmpData values ('Michael','Jones','Michael.j@email.com','France','22-Jan-1980')
GO
----------QUERY 1--------
DECLARE @SortByColumn varchar(20)='City',
@SortDirection varchar(5)='ASC'
SELECT EmpID,FName AS FirstName, LName AS LastName, Email, City, DOB as 'Date-Of-Birth'
FROM EmpData
ORDER BY
    CASE @SortDirection WHEN 'ASC' THEN
        CASE @SortByColumn
            WHEN 'FirstName' THEN FName
            WHEN 'LastName' THEN LName
            WHEN 'Email' THEN Email
            WHEN 'City' THEN City
            ELSE ''
        END
    END,
    CASE @SortDirection WHEN 'DESC' THEN
        CASE @SortByColumn
            WHEN 'FirstName' THEN FName
            WHEN 'LastName' THEN LName
            WHEN 'Email' THEN Email
            WHEN 'City' THEN City
            ELSE ''
        END
    END DESC
GO
----------QUERY 2--------
DECLARE @SortByColumn varchar(20)='City',
@SortDirection varchar(5)='ASC'
SELECT EmpID,FName AS FirstName, LName AS LastName, Email, City, DOB as 'Date-Of-Birth'
FROM EmpData
ORDER BY
        CASE @SortByColumn
            WHEN 'FirstName' THEN FName
            WHEN 'LastName' THEN LName
            WHEN 'Email' THEN Email
            WHEN 'City' THEN City
            ELSE ''
        END
        CASE @SortDirection
            WHEN 'DESC' THEN DESC
            ELSE ASC
        END

A `CASE` *expression* computes a scalar value (per row). It doesn't arbitrarily allow you to flip bits of syntax around. `DESC` and `ASC` are keywords - not *values*. — Damien_The_Unbeliever, Jan 02 '18 at 09:04

score 0 · Answer 1 · answered Jan 02 '18 at 10:40

Try like this

DECLARE @SortByColumn varchar(20)='City',
@SortDirection varchar(5)='asc'
;WITH CTE
AS
(
SELECT 
Seq1 = ROW_NUMBER() OVER(ORDER BY
        CASE @SortByColumn
            WHEN 'FirstName' THEN FName
            WHEN 'LastName' THEN LName
            WHEN 'Email' THEN Email
            WHEN 'City' THEN City
            ELSE ''
        END ASC),
Seq2 = ROW_NUMBER() OVER(ORDER BY
        CASE @SortByColumn
            WHEN 'FirstName' THEN FName
            WHEN 'LastName' THEN LName
            WHEN 'Email' THEN Email
            WHEN 'City' THEN City
            ELSE ''
        END DESC),
EmpID,FName AS FirstName, LName AS LastName, Email, City, DOB as 'Date-Of-Birth'
FROM EmpData
  )
  SELECT
    *
    FROM CTE
    ORDER BY CASE @SortDirection WHEN 'DESC' THEN Seq2 ELSE Seq1 END

DEMO

score 0 · Answer 2 · answered Jan 02 '18 at 10:41

It is better to use dynamic SQL in such cases

Declare @sql nvarchar(max)
declare @orderby nvarchar(100) = ' ORDER BY '
DECLARE @SortByColumn varchar(20) = 'City'
declare @SortDirection varchar(5) = 'ASC'

set @sql = '
SELECT EmpID,FName AS FirstName, LName AS LastName, Email, City, DOB as ''Date-Of-Birth''
FROM EmpData
' +
CASE @SortByColumn
    WHEN 'FirstName' THEN @orderby + ' FName ' + @SortDirection
    WHEN 'LastName' THEN @orderby + ' LName ' + @SortDirection
    WHEN 'Email' THEN @orderby + ' Email ' + @SortDirection
    WHEN 'City' THEN @orderby + ' City ' + @SortDirection
    ELSE ''
END

exec sp_executesql @sql

Since ASC or DESC is SQL clauses, we can not use them in CASE statements

Why CASE statement doesn't work without nesting?

2 Answers2