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.