Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Fixed Server Roles
Error Messages
Home > SQL Server Error Messages > Msg 141 - A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
SQL Server Error Messages - Msg 141 - A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

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'
Related Articles :