Solved

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

Posted on 2009-04-06
3
676 Views
Last Modified: 2012-05-06
Hi

Why the following SP make the error:

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


@id int
AS
BEGIN
Declare @clientid int
Declare @currencyid int
select @clientid = clientid, @currencyid = currencyid, amount from transactions where id = @id

Open in new window

0
Comment
Question by:techques
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24083950
because "@clientid = clientid, @currencyid = currencyid" in the select in assinging values to those variables and "amount" is trying to retrieve the amount value.  You can not do both in one statement.
0
 

Author Comment

by:techques
ID: 24083989
Does the following work?
Declare @clientid int
Declare @currencyid int
Declare @amount decimal (18,2)
select @clientid = clientid, @currencyid = currencyid, @amount = amount from transactions where id = @id

Open in new window

0
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 50 total points
ID: 24084076
Yes it should run and give you the values in your 3 variables.
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

739 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