How to use variable in ORDER BY clause in sql server

Let us assume we have created a table and inserted some data into using following sql query:

CREATE TABLE tblSource(

    ID INT IDENTITY PRIMARY KEY,

    vcData VARCHAR(500)

)

INSERT INTO tblSource VALUES('PC'),('Laptop'),('Mobile')

Variable in ORDER BY clause in sql server

DECLARE @Col_Name VARCHAR(128) = 'ID'

SELECT *

FROM tblSource 

ORDER BY CONVERT(SYSNAME,@Col_Name) DESC

Output:

ID	vcData
1	PC
2	Laptop
3	Mobile

Note: There is limitation of using varaible in ORDER BY clause. We cannot use column position in order by cluase. For example:

DECLARE @Position INT = 1

SELECT * FROM tblSource  ORDER BY @Position  DESC

  

We will get error message :

The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

What is Adhoc query in sql server: Cached Query Plan
Subquery vs inner join which one is better in sql server
Difference between actual execution plan and estimated execution plan in sql server
Best ways to pass comma delimited varchar variables to IN clause sql server