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

LVL 1
jmarkfoleyAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
appariConnect With a Mentor Commented:
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 PletcherConnect With a Mentor Senior 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
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.