Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : SQL Server Tutorial - SELECT Statement 101
Error Messages
Home > SQL Server Error Messages > Msg 1008 - 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.
SQL Server Error Messages - Msg 1008 - 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.

Error Message

Server: Msg 1008, Level 16, State 1, Line 1
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.

Causes

The ORDER BY clause of the SELECT statement, which is an optional clause, specifies the sort order used on columns returned in a SELECT statement. The syntax of the ORDER BY clause is as follows:

ORDER BY { <order_by_expression> [ COLLATE <collation_name> ] [ASC | DESC ] }[ , ... n ]

The <order_by_expression> specifies a column on which to sort the result. Multiple sort columns can be specified. The sequence of the sort columns in the ORDER BY clause defines the order of the sorted result set. A sort column can be specified as a name or column alias, or a non-negative integer representing the position of the name or alias in the select list. A sort column can include an expression but the expression cannot resolve to a constant, otherwise this error message will be raised.

Here’s a script that illustrates how this error message can be encountered:

CREATE TABLE [dbo].[Company] (
    [CompanyID]        INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    [CompanyName]      VARCHAR(100),
    [Address]          VARCHAR(100),
    [City]             VARCHAR(50),
    [State]            CHAR(2),
    [ZIPCode]          VARCHAR(10)
)
GO

INSERT INTO [dbo].[Company] ( [CompanyName], [Address], [City], [State], [ZIPCode] )
VALUES ( 'Microsoft', 'One Microsoft Way', 'Redmond', 'WA', '98052' )

INSERT INTO [dbo].[Company] ( [CompanyName], [Address], [City], [State], [ZIPCode] )
VALUES ( 'Google', '1600 Amphitheatre Pkwy', 'Mountain View', 'CA', '94043' )

INSERT INTO [dbo].[Company] ( [CompanyName], [Address], [City], [State], [ZIPCode] )
VALUES ( 'Facebook', '1 Hacker Way', 'Menlo Park', 'CA', '94025' )

INSERT INTO [dbo].[Company] ( [CompanyName], [Address], [City], [State], [ZIPCode] )
VALUES ( 'Twitter', '1355 Market St Suite 900', 'San Francisco', 'CA', '94103' )

INSERT INTO [dbo].[Company] ( [CompanyName], [Address], [City], [State], [ZIPCode] )
VALUES ( 'LinkedIn', '2029 Stierlin Court', 'Mountain View', 'CA', '94043' )
GO

DECLARE @ColumnIndex             INT

SET @ColumnIndex = 2

SELECT * FROM [dbo].[Company]
ORDER BY @ColumnIndex
GO
Msg 1008, Level 16, State 1, Line 6
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.

Solution / Work Around:

Changing the data type of the local variable used in the ORDER BY clause from an INT data type into a VARCHAR data type and specifying a column name in the local variable will not fix the issue, as can be seen in the following script:

DECLARE @ColumnName       VARCHAR(50)

SET @ColumnName = 'CompanyName'

SELECT * FROM [dbo].[Company]
ORDER BY @ColumnName
GO
Msg 1008, Level 16, State 1, Line 7
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 the error message means is that an actual column must be referenced in the ORDER BY instead of just the local variable. It does not mean that the name of the column being sorted will be the content of the local variable, as what was shown in the script.

Here’s an updated script that shows how to overcome this error message:

DECLARE @ColumnName       VARCHAR(50)

SET @ColumnName = 'CompanyName'

SELECT * FROM [dbo].[Company]
ORDER BY CASE @ColumnName
              WHEN 'CompanyName' THEN [CompanyName]
              WHEN 'Address' THEN [Address]
              WHEN 'City' THEN [City]
              WHEN 'State' THEN [State]
              WHEN 'ZIPCode' THEN [ZIPCode]
         END
GO

Here’s the output of this script:

CompanyID  CompanyName  Address                   City           State  ZIPCode
---------- ------------ ------------------------- -------------- ------ --------
3          Facebook     1 Hacker Way              Menlo Park     CA     94025
2          Google       1600 Amphitheatre Pkwy    Mountain View  CA     94043
5          LinkedIn     2029 Stierlin Court       Mountain View  CA     94043
1          Microsoft    One Microsoft Way         Redmond        WA     98052
4          Twitter      1355 Market St Suite 900  San Francisco  CA     94103

As can be seen from the script, the columns involved in the ORDER BY clause are all of string data types (VARCHAR and CHAR). If a numeric column is included in the ORDER BY such as the [CompanyID], an error message will be encountered, as can be seen from the following script:

DECLARE @ColumnName       VARCHAR(50)

SET @ColumnName = 'CompanyName'

SELECT * FROM [dbo].[Company]
ORDER BY CASE @ColumnName
              WHEN 'CompanyID' THEN [CompanyID]
              WHEN 'CompanyName' THEN [CompanyName]
              WHEN 'Address' THEN [Address]
              WHEN 'City' THEN [City]
              WHEN 'State' THEN [State]
              WHEN 'ZIPCode' THEN [ZIPCode]
         END
GO
Msg 245, Level 16, State 1, Line 6
Conversion failed when converting the nvarchar value 'Microsoft' to data type int.

This error occurs because when an operator, in this case the CASE statement, combines two or more expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. In this particular case, the INT data type has a higher precedence over a VARCHAR or CHAR data type. Therefore the VARCHAR or CHAR data type columns are being converted to an INT data type and generating the error in the process.

One way to overcome this error is to explicitly convert the INT data type column, in this case the [CompanyID], into a VARCHAR data type using the CAST or CONVERT function. Here’s an updated version of the script that overcomes the error.

DECLARE @ColumnName       VARCHAR(50)

SET @ColumnName = 'CompanyName'

SELECT * FROM [dbo].[Company]
ORDER BY CASE @ColumnName
              WHEN 'CompanyID' THEN CAST([CompanyID] AS VARCHAR(10))
              WHEN 'CompanyName' THEN [CompanyName]
              WHEN 'Address' THEN [Address]
              WHEN 'City' THEN [City]
              WHEN 'State' THEN [State]
              WHEN 'ZIPCode' THEN [ZIPCode]
         END
GO
Related Articles :