SQL Server 2008 introduced a new concept called Compound Assignment Operators. These operators have been available in many other programming languages for quite some time. Compound Assignment Operators allow you to perform an operation on a variable and assign the result in a single line of code.
Let’s take a look at an example to understand how Compound Assignment Operators work. Consider the following code without using Compound Assignment Operators:
DECLARE @myVar INT SET @myVar = 10 SET @myVar = @myVar * 5 SELECT @myVar AS MyResult
In the above code, we declare a variable @myVar and assign it a value of 10. Then, we multiply the value of @myVar by 5 and assign the result back to @myVar. Finally, we select the value of @myVar as MyResult.
This same operation can be achieved using Compound Assignment Operators, as shown in the following script:
DECLARE @myVar INT SET @myVar = 10 SET @myVar *= 5 SELECT @myVar AS MyResult
In this code, we use the Compound Assignment Operator *= to multiply the value of @myVar by 5 and assign the result back to @myVar. The final result is the same as in the previous example.
Here is a table of all the Compound Assignment Operators available in SQL Server:
| Operator | Action |
|---|---|
+= | Adds a specified amount to the original value and sets the original value to the result. |
-= | Subtracts a specified amount from the original value and sets the original value to the result. |
*= | Multiplies the original value by a specified amount and sets the original value to the result. |
/= | Divides the original value by a specified amount and sets the original value to the result. |
%= | Divides the original value by a specified amount and sets the original value to the modulo. |
&= | Performs a bitwise AND operation and sets the original value to the result. |
^= | Performs a bitwise exclusive OR operation and sets the original value to the result. |
|= | Performs a bitwise OR operation and sets the original value to the result. |
Using Compound Assignment Operators can make your code more concise and easier to read. It allows you to perform arithmetic or bitwise operations on variables in a single line, reducing the need for multiple lines of code.
Next time you find yourself performing an operation on a variable and assigning the result back to the same variable, consider using Compound Assignment Operators to simplify your code.