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