ORDER BY with a CASE statement for column with alias

Question

I need a stored procedure which will allow me to return sorted results based on two input parameters: @sortColumnName and @sortDirection. I wrote the following stored procedure, but when I run it, I am getting this error: "Invalid column name 'LastPayCheckDate'."

SELECT Name, SUM(Pay), MAX(PayCheckDate) as LastPayCheckDate
FROM Employee
GROUP BY Name
ORDER BY 
    CASE WHEN @sortColumnName = 'LastPayCheckDate' AND @sortDirection = 'ASC'
        THEN [LastPayCheckDate] END ASC,
    CASE WHEN @sortColumnName = 'LastPayCheckDate' AND @sortDirection = 'DESC'
        THEN [LastPayCheckDate] END DESC

What is going on? I suppose that t-sql runs the case statement before the select... Am I right? How can I work around this issue?

Thanks for the help!

Answer 1

Try this

ORDER BY 
    CASE WHEN @sortColumnName = 'LastPayCheckDate' AND @sortDirection = 'ASC'
        THEN MAX(PayCheckDate) END ASC,
    CASE WHEN @sortColumnName = 'LastPayCheckDate' AND @sortDirection = 'DESC'
        THEN MAX(PayCheckDate) END DESC

Example

create table Test (id int, somevalue int)

insert Test values(1,1)
insert Test values(2,1)
insert Test values(3,2)
insert Test values(3,2)
insert Test values(4,2)

run this in 1 shot

declare @sortDirection char(4)
select  @sortDirection = 'DESC'

select somevalue, COUNT(*)
 from Test
 group by somevalue 
 order by case when @sortDirection = 'ASC'
 then COUNT(*) end asc,
 case when @sortDirection = 'DESC'
 then COUNT(*) end desc

 select  @sortDirection = 'ASC'
 select somevalue, COUNT(*)
 from Test
 group by somevalue 
 order by case when @sortDirection = 'ASC'
 then COUNT(*) end asc,
 case when @sortDirection = 'DESC'
 then COUNT(*) end desc

Answer 2

You need to either use the function again or use a subquery if you want to be able to refer to the column alias.

Also, I think that you need to make sure that all of your columns in the case statement get converted to the same data type.