Friday, August 21, 2009

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

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.



This can be made clear with the help of following example. For example if you are running following SELECT statement in the northwend 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'

9 comments:

  1. simple but very usefull.

    ReplyDelete
  2. Thanks, this explanation saved me.

    ReplyDelete
  3. Thanks for the "dummies" explanation, it saved me some banging-the-head-into-the-brick-time. :)

    ReplyDelete
  4. Isn't the SELECT query in your last section the exact same query as the one they said you should NOT use?:

    SELECT @CompanyName = [CompanyName],
    @ContactName = [ContactName],
    [ContactTitle]
    FROM [dbo].[Customers]
    WHERE [CustomerID] = 'ALFKI'

    In other words, you are still using the "[ContactTitle]" in the SELECT list without a variable being assigned to it. So, it still looks like it will not work.

    This is the same article as found at: http://www.sql-server-helper.com/error-messages/msg-141.aspx

    ReplyDelete
    Replies
    1. You're right. I'm still looking for a working solution to this.

      Delete
  5. Come on, you just steal articles from others without giving them credit?
    http://www.sql-server-helper.com/error-messages/msg-141.aspx

    ReplyDelete
  6. this blog is created just for revenue on ads.. even the title is spelled wrong..

    ReplyDelete