The problem is that by using a variable that way, the result is the same as if your query were:
[font="Courier New"]SELECT e.C1, e.C2, e.C3, e.C4, ROW_NUMBER() OVER(ORDER BY 'C1') as RowNum
FROM T1 e[/font]
The constant value 'C1' is the same for every row, so no additional sorting can be done.
Your problem can be solved with a CASE expression:
[font="Courier New"]SELECT e.C1, e.C2, e.C3, e.C4,
ROW_NUMBER() OVER(ORDER BY CASE @sortBy
WHEN 'C1' THEN C1
WHEN 'C2' THEN C2
WHEN 'C3' THEN C3
ELSE C4
END) as RowNum
FROM T1 e[/font]
If C1, C2, C3, and C4 have different data types (date vs character vs numeric), then you will need to covert them all to a common type, usually a string type. For example, if C1 is varchar(100), C2 is datetime, C3 is int, and C4 is varchar(50), then the CASE clause would look like:
ORDER BY CASE @sortBy
WHEN 'C1' THEN C1
WHEN 'C2' THEN convert(varchar(100), C2, 120)
WHEN 'C3' THEN convert(varchar(100), C3)
ELSE C4
-Eddie