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),
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