Compound Operators in SQL Server 2008

SQL Server 2008 introduced the new feature compound operator. Compound operators are available in other programming languages like C# etc. Compound Assignment Operators are operators where variables are operated upon and assigned on the same line.

The following Operators are supported as compound operators:

Operator	Description
+=	It will Add some amount to the original value and store result in to original value
-=	It will subtract some amount to the original value and store result in to original value
*=	It will multiply  some amount to the original value and store result in to original value
/=	It will divide  some amount to the original value and store result in to original value
%=	It will divide  some amount to the original value and store result in to ordinal value to the modulo.
&=	It will perform a bitwise AND and sets the original value to the result.
^=	It will perform a bitwise exclusive OR and sets the original value to the result.
|=	It will perform a bitwise  OR and sets the original value to the result.

Examples

+= Operator

DECLARE @addvalue int = 53;
SET @addvalue += 20 ;
PRINT 'Add value :' + CAST(@addvalue AS VARCHAR);

--Result : Add value :73 

DECLARE @concString VARCHAR(50) = 'Jignesh';
SET @concString += ' Trivedi' ;
PRINT 'Output :' + @concString;

--Result : Output :Jignesh Trivedi

-= Operator

DECLARE @subValue int = 99;
SET @subValue -= 2 ;
PRINT 'subtract value :' + CAST(@subValue AS VARCHAR);

--Result : subtract value :97

*= Operator

DECLARE @mulValue int = 75;
SET @mulValue *= 20 ;
PRINT 'Multiplication :' + CAST(@mulValue AS VARCHAR);

--Result : Multiplication :1500

/= Operator

DECLARE @divValue NUMERIC(8,2) = 27;
SET @divValue /= 2.5 ;
PRINT 'Division :' + CAST(@divValue AS VARCHAR);

--Result : Division :10.80

%= Operator

DECLARE @modulo int = 25;
SET @modulo %= 5 ;
PRINT 'Modulo :' + CAST(@modulo AS VARCHAR);

--Result : Modulo :1

&= Operator

DECLARE @bitAnd int = 90;
SET @bitAnd &= 13 ;
PRINT 'Bitwise AND Operation:' + CAST(@bitAnd AS VARCHAR);

--Result : Bitwise AND Operation:8

^=Operator

DECLARE @bitExOr int = 244;
SET @bitExOr ^= 20 ;
PRINT 'Bitwise Exclusive OR Operation:' + CAST(@bitExOr AS VARCHAR);

--Result : Bitwise Exclusive OR Operation:224

|= Operator

DECLARE @bitOR int = 270;
SET @bitOR |= 25 ;
PRINT 'Bitwise OR Operation:' + CAST(@bitOR AS VARCHAR);

--Result : Bitwise OR Operation:287

The Compound Operators feature is enhanced in SQL Server 2008. They are like compound operators in languages like C, C++, and C #. Compound operators are a combination of operator with another operator.

About Author
Rajratna Shelhalkar is part of Systems Plus technology Think Tank and is very keen to resolve challenges using his technical skills. He works in Systems Plus and actively contributes to technology. He can be contacted at: rajratna.s@spluspl.com