Error Message:
Msg 116, Level 16, State 1, Line 2
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Severity level:
16.
Description:
This error message appears, when a subquery that is not introduced with EXISTS contains more that one expression in the SELECT list.
Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.
Resolution:
Error of the Severity Level 15 are generated by the user and can be fixed by the SQL Server user. You need to remove all but one expression from the SELECT list.
Versions:
All versions of SQL Server.
Example(s):
USE Northwind
SELECT t1.*
FROM dbo.Orders t1
WHERE t1.OrderDate =
(SELECT MAX(OrderDate), CustomerID
FROM dbo.Orders
WHERE t1.CustomerID = CustomerID
GROUP BY CustomerID)
Remarks:
In the above example we try to retrieve the most current order from each customer. The correlation between the subquery and the outer SELECT is created via t1.CustomerID = CustomerID. The error is raised, because we entered two expressions in the SELECT list of the subquery (MAX(OrderDate), CustomerID). To turn the query into a syntactically correct one, we need to modify the query to:
USE Northwind
SELECT t1.*
FROM dbo.Orders t1
WHERE t1.OrderDate =
(SELECT MAX(OrderDate)
FROM dbo.Orders
WHERE t1.CustomerID = CustomerID)