# Examples of the compound assignment operator, sql server 2012

Q1: Is it possible to use += inside select statement (see example 1 below)? Something like:
declare @v decimal(5,2) =12;
Set @v +=5;
select  (@v +=5) as Val
to display
Val
---
17

Q2: Could you provide a working sample for examples 6 through 9 (shown below, using Select or just like the solution given for examples 1 through 5)?

Examples:
``````--1) += (add)
declare @v decimal(5,2) =12;
Set @v +=5;
select  @v    -- 17

--2) -= (subtract)
declare @v decimal(5,2) =12;
Set @v -=5;
select  @v    -- 7

--3) *= (multiply)
declare @v decimal(5,2) =12;
Set @v *=5;
select  @v    -- 60

--4) %= (modulo)
declare @v decimal(5,2) =12;
Set @v %=5;
select  @v    -- 2

--5) += (concatenate).
declare @v varchar(5) ='12';
Set @v +='5';
select  @v
-- 125

--6) &= (bitwise and),

--7) |= (bitwise or),

--8) ^= (bitwise xor),

--9) += (concatenate).
``````
LVL 34
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
A1: No, you cannot use the compound variable in the select statement

A2: These examples are straight from MSDN
``````DECLARE @x5 int = 27;
SET @x5 %= 2 ;
SELECT @x5 AS Modulo_of_27_divided_by_2;

DECLARE @x6 int = 9;
SET @x6 &= 13 ;
SELECT @x6 AS Bitwise_AND;

DECLARE @x7 int = 27;
SET @x7 ^= 2 ;
SELECT @x7 AS Bitwise_Exclusive_OR;

DECLARE @x8 int = 27;
SET @x8 |= 2 ;
SELECT @x8 AS Bitwise_OR;
``````
(you have provided an example for concatenate)
MSSQL Senior EngineerCommented:
Q1: Only the usual way:
``````declare @v decimal(5,2) =12;
select @v = @v+5
print @v
``````

Q2: The last one (9-concatenate) isn't what you have on 5 (+=)?
Q1: Is it possible to use += inside select statement (see example 1 below)? Something like:
declare @v decimal(5,2) =12;
Set @v +=5;
select  (@v +=5) as Val
to display
Val
---
17

No, it is not possible.  That is a C++ type short hand for "set @V = @V + 5".

Use this:

``````declare @v decimal(5,2) =12;
declare @Val decimal(5,2) =12;

Set @v = @v + 5;
select @Val = @v
print @Val -- or just print @v
``````

Q2: Could you provide a working sample for examples 6 through 9 (shown below, using Select or just like the solution given for examples 1 through 5)?

Examples:
--2) -= (subtract)
--3) *= (multiply)
--4) %= (modulo)
--5) += (concatenate).

--6) &= (bitwise and),
``````declare @v int
declare @V_and int
set @v = 5				-- 5 = 0101
set @V_and = @v & 4		-- 4 = 0100
select @V_and
set @V_and = @v & 2		-- 2 = 0010
select @V_and
``````

--7) |= (bitwise or),
``````declare @v int
declare @V_or int
set @v = 5				-- 5 = 0101
set @V_or = @v | 4		-- 4 = 0100
select @V_or
set @V_or = @v | 2		-- 2 = 0010
select @V_or
``````

--8) ^= (bitwise xor),
``````declare @v int
declare @V_xor int
set @v = 5				-- 5 = 0101
set @V_xor = @v ^ 4		-- 4 = 0100
select @V_xor
set @V_xor = @v ^ 2		-- 2 = 0010
select @V_xor
``````

--9) += (concatenate).
``````declare @v_string varchar(50) --must be character type
set @v_string = 'First part of string'
set @v_string = @v_string + ', concatenated part.'
select @v_string
``````

* Is this homework?
Senior DBACommented:
Q1) Yes.  You just can't assign a column name to the result:

declare @v decimal(5,2) =12;
declare @x varchar(30) = 'abc';
select  @v += 5, @x += 'def'
select @v, @x
Nice one Scott,

(I swear, I learn something every day I'm on Expert's Exchange (often from Scott).  And I've been doing SQL for a LONG time.)

So it seems for numeric and string data types, you can do compound math/assignment operators or compound concat/assignment operator .

However, this does not seem to work with bitwise operators.   (That makes sense to me.)

-gg
Senior DBACommented:
Standard bitwise operators & and | work as well.
declare @int int
select @int = 1
select @int |= 4
select @int
select @int &= 1
select @int

Note that the restriction on having a name for a variable assignment is standard SQL and is not related to the combined operators.  You could never  mix assignment and output in the same SELECT.

declare @vc varchar(30)
select @vc = 'abc'
select @vc = 'abc' as col1

Experts Exchange Solution brought to you by