I thought this SQL select statement works, right?

declare @status int

select field_1, @status = field_status
from mytable
where field_status = 2 and field_3 = 4

I thought this SQL select statement works, right?

Instead, I receive an error about "@status = field_status".

Any opinion?
LVL 1
chuang4630Asked:
Who is Participating?
 
rafranciscoCommented:
With your SQL statement, you will get the following error:

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.

Try removing the field_1 from your statement

declare @status int

select @status = field_status
from mytable
where field_status = 2 and field_3 = 4
0
 
chuang4630Author Commented:
Error message: SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hi chuang4630,


this is wrong You are not supposed use like this

select  @status = field_status
from mytable
where field_status = 2 and field_3 = 4

the above statement will work





Aneesh R!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
chuang4630Author Commented:
So I have to do assignement for every field, right?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
You can use like this

SELECT @Filed1 = Col1,
            @Fileld2 = Col2
FROM urTable
Where Some Condition
0
 
rafranciscoCommented:
Yes, you have to do assignment for every field, or if you don't need it, remove it from the SELECT statement.  You may also refer to the following links:

http://www.sql-server-helper.com/error-messages/msg-141.aspx
http://www.sql-server-helper.com/faq/error-messages-p05.aspx
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
In ur case it is

declare @status int
declare @Field1 varchar(100)  --- change the datatype

select @Field1 = field_1,
         @status = field_status
from mytable
where field_status = 2 and field_3 = 4
0
 
chuang4630Author Commented:
Although there are many correct answers, the accepted answer is the first one came out.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.