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

Open in new window

LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

chaauCommented:
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;

Open in new window

(you have provided an example for concatenate)
Vitor MontalvãoMSSQL Senior EngineerCommented:
Q1: Only the usual way:
declare @v decimal(5,2) =12;
select @v = @v+5
print @v

Open in new window


Q2: The last one (9-concatenate) isn't what you have on 5 (+=)?
G GodwinDatabase AdministratorCommented:
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

Open in new window


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

Open in new window


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

Open in new window


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

Open in new window


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

Open in new window


* Is this homework?
SolarWinds® VoIP and Network Quality Manager(VNQM)

WAN and VoIP monitoring tools that can help with troubleshooting via an intuitive web interface. Review quality of service data, including jitter, latency, packet loss, and MOS. Troubleshoot call performance and correlate call issues with WAN performance for Cisco and Avaya calls

Scott PletcherSenior 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
G GodwinDatabase AdministratorCommented:
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
Scott PletcherSenior 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

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
G GodwinDatabase AdministratorCommented:
Great to know.
-GDG  :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.