The SELECT item identified by the ORDER BY number 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.

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')

If we will execute following sql query:

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.

Cause: When we use variable in the ORDER BY clause then it must be a column name. It cannot be column position.

Solution:

Use the column name instead of column position. For example:

DECLARE @Col_Name VARCHAR(128) = 'ID'

SELECT *

FROM tblSource 

   ORDER BY CASE 

    WHEN @Col_Name = 'ID' THEN CAST(ID AS SQL_VARIANT)

    WHEN @Col_Name = 'vcData' THEN CAST(vcData AS SQL_VARIANT) END  ASC

Output:

ID	vcData
1	PC
2	Laptop
3	Mobile