|
|
Error Message
Server: Msg 141, Level 15, State 1, Line 1
A SELECT statement that assigns a value to a variable
must not be combined with data-retrieval operations.
Causes
This error occurs when you are assigning the column values from a SELECT statement into local variables but not all columns are assigned to a corresponding local variable.
|
To illustrate, using the [dbo].[Customers] table in the Northwind database, the following SELECT statement will generate the error:
DECLARE @CompanyName NVARCHAR(40)
DECLARE @ContactName NVARCHAR(30)
SELECT @CompanyName = [CompanyName],
@ContactName = [ContactName],
[ContactTitle]
FROM [dbo].[Customers]
WHERE [CustomerID] = 'ALFKI'
The error is caused by the [ContactTitle] because it is not assigned to a local variable.
Solution / Work Around:
To avoid this error, make sure that all columns are assigned to a local variable.
DECLARE @CompanyName NVARCHAR(40)
DECLARE @ContactName NVARCHAR(30)
DECLARE @ContactTitle NVARCHAR(30)
SELECT @CompanyName = [CompanyName],
@ContactName = [ContactName],
@ContactTitle = [ContactTitle]
FROM [dbo].[Customers]
WHERE [CustomerID] = 'ALFKI'
Alternatively, if you will not be using the column, simply remove it from the SELECT statement.
DECLARE @CompanyName NVARCHAR(40)
DECLARE @ContactName NVARCHAR(30)
SELECT @CompanyName = [CompanyName],
@ContactName = [ContactName]
FROM [dbo].[Customers]
WHERE [CustomerID] = 'ALFKI'
If you really need to do both, meaning to assign the value to local variables and to return the columns as a result set, you have to do it in 2 steps instead of combining them into one SELECT statement:
DECLARE @CompanyName NVARCHAR(40)
DECLARE @ContactName NVARCHAR(30)
SELECT @CompanyName = [CompanyName],
@ContactName = [ContactName],
[ContactTitle]
FROM [dbo].[Customers]
WHERE [CustomerID] = 'ALFKI'
SELECT [ContactTitle]
FROM [dbo].[Customers]
WHERE [CustomerID] = 'ALFKI'
|