<pre lang="sql">declare @sortorder varchar(10) = 'desc';
declare @sortexpression varchar(10) = 'ename';
select *, ROW_NUMBER() over (order by case @sortorder
when 'asc' then case @sortexpression
-- changed to VARCHAR to match all sort types
when 'empno' then CAST([EmployeeID] AS VARCHAR(10))
when 'ename' then [LastName]
end end asc,
case @sortorder
when 'desc' then case @sortexpression
when 'empno' then CAST([EmployeeID] AS VARCHAR(10))
when 'ename' then [LastName]
end end desc )
from [AdventureWorks2008R2].[dbo].[MyEmployees]
Try running this with various values for your variables. The sort is done in order first, second.
If you want to run with only one order value then the CASE statements would need changed.