Sharing of thoughts and information is what blogging is all about. This way we can learn from each other. Post A Comment!These postings are provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.
Chris Di LulloSr. IT Pro Marketing Manager Twitter | LinkedIn Pierre Roman Twitter | LinkedIn Mitch Garvis Twitter | LinkedIn Anthony Bartolo Twitter | LinkedIn
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.
if variable @var1 equals 50,
@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.
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';
DECLARE @Var2 int = 10;
SELECT @Var2 -= 5;
SELECT @Var2 as 'Value';
DECLARE @Var3 int = 10;
SELECT @Var3 *= 10 ;
SELECT @Var3 as 'Value';
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.