Msg 141 - A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

If you are assigning column values from a Select to some local values but not all columns are assigned to a corresponding local variable the following error message will come.


Server: Msg 141, Level 15, State 1, Line 2
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.


For example if you are running following SELECT statement in the northwind database will generate 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.


To avoid this error you declare a local variable called contact title and assign to the contacttitle column


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'