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'
simple but very usefull.
ReplyDeleteVery Very useful
ReplyDeleteThanks, this explanation saved me.
ReplyDeleteThanks for the "dummies" explanation, it saved me some banging-the-head-into-the-brick-time. :)
ReplyDeletethanks~useful
ReplyDeleteIsn't the SELECT query in your last section the exact same query as the one they said you should NOT use?:
ReplyDeleteSELECT @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
You're right. I'm still looking for a working solution to this.
DeleteCome on, you just steal articles from others without giving them credit?
ReplyDeletehttp://www.sql-server-helper.com/error-messages/msg-141.aspx
this blog is created just for revenue on ads.. even the title is spelled wrong..
ReplyDeleteweak sauce
ReplyDelete