SQL SERVER 2008 has introduced a new concept of Compound Assignment Operators. Compound Assignment Operators are available in many other programming languages for quite some time. Compound Assignment Operators are operated where variables are operated upon and assigned in the same line. Compound-assignment operators provide a shorter syntax for assigning the result of an arithmetic or bitwise operator. They perform the operation on the two operands before assigning the result to the first operand.
Let us see the following operation without using Compound Assignment Operators.
DECLARE @myVar INT
SET @myVar = 10
SET @myVar = @myVar * 5
SELECT @myVar AS MyResult
GO
Above operation can be done using Compound Assignment Operators as demonstrated in following script.
DECLARE @myVar INT
SET @myVar = 10
SET @myVar *= 5
SELECT @myVar AS MyResult
GO
Here is the table of all the compound operators in SQL Server.
| Operator | Action |
|---|---|
| += | Adds some amount to the original value and sets the original value to the result. |
| -= | Subtracts some amount from the original value and sets the original value to the result. |
| *= | Multiplies by an amount and sets the original value to the result. |
| /= | Divides by an amount and sets the original value to the result. |
| %= | Divides by an amount and sets the original value to the modulo. |
| Performs a bitwise AND and sets the original value to the result. | |
| ^= | Performs a bitwise exclusive OR and sets the original value to the result. |
| |= | Performs a bitwise OR and sets the original value to the result. |
Where Compound Assignment Operators Help and Where They Do Not
They are not only for variables. You can use them in an UPDATE too, for example UPDATE Product SET Stock -= 1 WHERE ProductID = 10, which reads nicely. The += operator also works with strings, so SET @List += ', ' + @Name appends text to the end.
Two things to remember. If the variable is NULL, the result stays NULL, because NULL plus anything is NULL. Give the variable a starting value first. And this syntax needs SQL Server 2008 or later, so a script that uses it will fail on SQL Server 2005. If your code must run on both, stay with the long form. The bitwise ones, such as &= and |=, follow the same rules and are handy for flag columns.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
