Compound Operators in SQL Server

Introduction

SQL Server 2008 has 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.

operation.gif

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

Conclusion

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


Similar Articles