?
Solved

I thought this SQL select statement works, right?

Posted on 2006-05-22
8
Medium Priority
?
1,759 Views
Last Modified: 2008-01-09
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?
0
Comment
Question by:chuang4630
  • 3
  • 3
  • 2
8 Comments
 
LVL 28

Accepted Solution

by:
rafrancisco earned 2000 total points
ID: 16736409
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
 
LVL 1

Author Comment

by:chuang4630
ID: 16736412
Error message: SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16736413
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 1

Author Comment

by:chuang4630
ID: 16736426
So I have to do assignement for every field, right?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16736438
You can use like this

SELECT @Filed1 = Col1,
            @Fileld2 = Col2
FROM urTable
Where Some Condition
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 16736445
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16736448
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
 
LVL 1

Author Comment

by:chuang4630
ID: 16736667
Although there are many correct answers, the accepted answer is the first one came out.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question