• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3362
  • Last Modified:

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

I've been programming SLQ Server queries for years and I believe this is the first time I've seen this error. I have the query show below, two select statements. The first select runs just fine and returns values. When the 2nd select executes I get the error:

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

I don't get this message at all. The first select also does a data retrieval operation so just the fact that a data retrieval operation is being performed must not be the whole problem. If I remove the ',@SURpensionTotal = ' bit, it runs OK (but that doesn't do me much good).

What am I doing wrong? Please help!

declare @memberId varchar(11),
            @retireeId varchar(11),
            @retireePension money,
           @SURpensionTotal money

set @memberId = '048011'

select @retireeId = m.retireeId, @retireePension = r.retireePension
from members m
join (select e.memberId, sum(e.amount) retireePension
  from memberEarnings e
  join earningCodes c on c.earningCode = e.earningCode and e.earningCode in ('DRP','PEN')
  where e.enabled = 1 and e.oneShot = 0
  group by e.memberId) r on r.memberId = m.retireeId
where m.memberId = @memberId

select m.retireeId,@SURpensionTotal = sum(e.amount)
from members m
join memberEarnings e on e.memberId = m.memberId and e.enabled = 1 and e.oneShot = 0
  and e.earningCode in ('DRP','PEN')
where (m.terminationDate is null 
  or (dbo.firstOfThisMonth(default) <= m.terminationDate and dbo.firstOfNextMonth(default) > m.terminationDate))
  and m.retireeId = @retireeId
group by m.retireeId

Open in new window

0
jmarkfoley
Asked:
jmarkfoley
2 Solutions
 
appariCommented:
you cannot combine both assigning value to a variable and data selection in same select statement. you can change your second sql to

select @SURpensionTotal = sum(e.amount)
from members m
join memberEarnings e on e.memberId = m.memberId and e.enabled = 1 and e.oneShot = 0
  and e.earningCode in ('DRP','PEN')
where (m.terminationDate is null
  or (dbo.firstOfThisMonth(default) <= m.terminationDate and dbo.firstOfNextMonth(default) > m.terminationDate))
  and m.retireeId = @retireeId
group by m.retireeId
0
 
Scott PletcherSenior DBACommented:
Or to:

SELECT @retireeId = m.retireeId, @SURpensionTotal = sum(e.amount)


"Data retrieval" in this case means data retrieved to return to the caller.  Variable assignments never return data to the caller.

For example, a stored proc returns a result set to the caller.
So:

SELECT m.retireeId, sum(e.amount)
FROM ...

in a stored proc would return that result to the caller, whereas:

SELECT @var1 = m.retireeId, @var2 = sum(e.amount)
FROM ...

would not.
0
 
jmarkfoleyAuthor Commented:
duh! Of course! That makes total sense. I've probably not seen this error before because I haven't made this mistake before. ScottPletcher's solution is what I did in my 1st query. What I need for this query is appari's solution. I don't need to return the retireeId again. I guess I just stuck it in the SELECT out of habit of putting GROUP BY columns in the select list (but that's not needed). Thanks!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now