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

SQL Server 49 Views 2 Min Read

In this blog post, let’s learn about the error message “141 – A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.” in Microsoft SQL Server, the reason why it appears and the solution to fix it.

SQL Server Error Message

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

Reason for the Error

SQL Server Error Msg 141 happens when a SELECT statement is paired with data-retrieval activities, such as retrieving data into a variable. This error arises because a SELECT statement that assigns a value to a variable cannot be coupled with data-retrieval activities.

DECLARE @myVariable INT

SELECT @myVariable = [CustomerID],[FirstName]
FROM [AdventureWorks2019].[dbo].[Customers]

In this example, the SELECT statement is attempting to assign the value of CustomerIDto @myVariable, but it is also selecting the FirstName. This causes the SQL Server to throw error Msg 141 because a SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

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

Solution

To resolve this error, separate the SELECT statement that assigns the value to the variable from the SELECT statement that retrieves data. Here’s an example of how to do this:

DECLARE @myVariable INT

SELECT @myVariable = [CustomerID]
FROM [AdventureWorks2019].[dbo].[Customers]

SELECT @myVariable
Tags: