Error in while using function that use in select statement in order by case in SQL Server

Question

I have table structure below and i want to use function that i used in select statement in order by using case keyword.But i got the error as saying that FullName is invalid column.

CREATE TABLE STUDENTS
(
     ID INT IDENTITY(1, 1) ,
     FirstName VARCHAR(50) ,
     LastName VARCHAR(50)
)

Insert some data to the table STUDENTS

INSERT  INTO STUDENTS
VALUES  ( 'kamal', 'sharma' )

INSERT  INTO STUDENTS
VALUES  ( 'john', 'jack' )

INSERT  INTO STUDENTS
VALUES  ( 'Rahul', 'Thomson' )

This statement is working

SELECT 
       ID
     , FirstName + ' ' + LastName AS FullName
FROM STUDENTS
ORDER BY FullName ASC

I need this one but it says FullName is invalid column.

DECLARE @VAL INT  
SET @VAL = 1

SELECT ID
    ,FirstName + ' ' + LastName AS FullName
FROM STUDENTS
ORDER BY CASE 
        WHEN @VAL = 1
            THEN FullName
        END ASC
    ,CASE 
        WHEN @VAL = 2
            THEN FullName
        END DESC

Msg 207, Level 16, State 1, Line 10
Invalid column name 'FullName'.
Msg 207, Level 16, State 1, Line 14
Invalid column name 'FullName'.

Thanks in advance for any help.

Answer 1

Column aliases defined in the SELECT can not be used inside expressions in the corresponding ORDER BY.

Either repeat the underlying definition.

SELECT ID
    ,FirstName + ' ' + LastName AS FullName
FROM STUDENTS
ORDER BY CASE 
        WHEN @VAL = 1
            THEN FirstName + ' ' + LastName
        END ASC
    ,CASE 
        WHEN @VAL = 2
            THEN FirstName + ' ' + LastName
        END DESC

Or define the alias at a different level. e.g. derived table or CTE.

WITH T
     AS (SELECT ID,
                FirstName + ' ' + LastName AS FullName
         FROM   STUDENTS)
SELECT *
FROM   T
ORDER  BY CASE
            WHEN @VAL = 1 THEN FullName
          END ASC,
          CASE
            WHEN @VAL = 2 THEN FullName
          END DESC 

Answer 2

Change it to this:

SELECT ID
    ,FirstName + ' ' + LastName AS FullName
FROM STUDENTS
ORDER BY CASE 
        WHEN @VAL = 1
            THEN FirstName + ' ' + LastName
        END ASC
    ,CASE
        WHEN @VAL = 2
            THEN FirstName + ' ' + LastName
        END DESC