CM2K CM2K - 1 month ago 9
SQL Question

SQL Server - Adding up variables combined with data-retrival operations

When I run the code below I get this error:


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


Please see my code below:

DECLARE @M1 float
DECLARE @M2 float
declare @M3 float
DECLARE @M4 float
DECLARE @M5 float
DECLARE @M6 float
DECLARE @M7 float
DECLARE @M8 float
DECLARE @M9 float
DECLARE @M10 float
DECLARE @M11 float
DECLARE @M12 float
declare @theSum float

set @M1 = 0
set @m2 = 1954286
set @M3 = 1954286
set @M4 = 0
set @M5 = 0
set @M6 = 0
set @M7 = 0
set @M8 = 0
set @M9 = -11725714
set @M10 = 1954286
set @M11 = 1954286
set @theSum = @M1+@M2+@M4+@M5+@M6+@M7+@M8+@M9+@M10+@M11+@M12

SELECT @theSum,

@M3 = case
-- when M3 = 0 then 0
when @M1+@M2+@M4+@M5+@M6+@M7+@M8+@M9+@M10+@M11+@M12 != 0 then @M3-@M2 -- mvc
else
case when @M2 != 0 then @m3-@m2
when @M1 != 0 then @m3-@m1
when @M12 != 0 then @m3-@m12
when @M11 != 0 then @m3-@m11
when @M10 != 0 then @m3-@m10
when @M9 != 0 then @m3-@m9
when @M8 != 0 then @m3-@m8
when @M7 != 0 then @m3-@m7
when @M6 != 0 then @m3-@m6
when @M5 != 0 then @m3-@m5
when @M4 != 0 then @m3-@m4

else @m3
end
end


It's not necessary to explain what I need to achieve, all i want is an example where you can assign values to variables and then use them in a case statement like in the example above. Any example would be much appreciated.

Answer

I think the error message is pretty clear: a query either assigns variables or returns a result set, but not both.

These are incompatible:

SELECT  @theSum,
        @M3   = case . . .

Perhaps you want:

SELECT @M3 = case . . . ;

SELECT @theSum, @M3;