“The SQL Guy” Post #4: SQL SERVER NOW SUPPORTS COMPOUND OPERATORS
Saleem Hakani switches gears a bit to talk about how to make code more efficient with compound operators in SQL Server 2008. Enjoy!
Do you miss the ability to use the compound operators the way you would use them in other languages? Wouldn’t it be nice to provide a shorter syntax for assigning the result of an arithmetic operator?
SQL Server 2008 introduces Compound Operators that allows you to perform the operation on the two operands before assigning the result to the first operand.
For example:
if variable @var1 equals 50,
then
@var1 += 50
This will take the original value of @var1 (which is 50), and adds 50 and sets @var1 to that new value which will be 100 in the above case.
OPERATOR |
ACTION |
+= |
Adds the value to the original value and sets the new value to the result. |
-= |
Subtracts the original value and sets the new value by subtracting original with the new one. |
*= |
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. |
Here are some examples that use Compound Operators in SQL Server:
Addition using Compound Operator |
Subtraction Using Compound Operator |
Multiplication Using Compound Operator |
DECLARE @Var1 int = 10; SELECT @Var1 += 10; SELECT @Var1 as 'Value'; --Returns 20 |
DECLARE @Var2 int = 10; SELECT @Var2 -= 5; SELECT @Var2 as 'Value'; --Returns 5 |
DECLARE @Var3 int = 10; SELECT @Var3 *= 10 ; SELECT @Var3 as 'Value'; --Returns 100 |
As you can see from the above scripts, it becomes easy to perform an operation on two operands before assigning the result to the first operand.
