sql server - SQL dynamic ORDER BY using alias -

using sql server, can order normal select query using alias:

select       u.firstname + ' ' + u.lastname physicianname, count(r.id) referralscount         referrals r  inner join    users u on r.physicianid = u.id group     r.physicianid, u.firstname, u.lastname order     physicianname 

however, attempting same thing dynamic order by:

select       u.firstname + ' ' + u.lastname physicianname, count(r.id) referralscount         referrals r  inner join    users u on r.physicianid = u.id group     r.physicianid, u.firstname, u.lastname order      case when @orderby = 'physicianname' physicianname end,     case when @orderby = 'referralscount' referralscount end 

produces following error:

msg 207, level 16, state 1, line 10
invalid column name 'physicianname'.
msg 207, level 16, state 1, line 11 invalid column name 'referralscount'.

column aliases defined in select can used in order by on own. not in expression.

you can adjust original attempt follows.

;with t      (select u.firstname + ' ' + u.lastname physicianname,                 count(r.id)                    referralscount            referrals r                 inner join users u                   on r.physicianid = u.id          group  r.physicianid,                    u.firstname,                    u.lastname) select *   t order  case             when @orderby = 'physicianname' physicianname           end,           case             when @orderby = 'referralscount' referralscount           end