More on SQL Server 2008 Variable Enhancements

Let's continue where we left last week. I want to point out another enhancement around assigning values to parameters in SQL 2008 and above. It's called compound assignment operator, starting with SQL 2008 we can declare a parameter and assign value and also we can do mathematical operations.

-- SQL Server 2008 and 2008 R2

DECLARE @DueAmount MONEY = 101

-- 10% off purchases over 100$ promotion

IF @DueAmount > 100

BEGIN

SET @DueAmount *= 0.9

END

SELECT @DueAmount

GO

-- SQL Server 2005 and before

DECLARE @DueAmount MONEY

SELECT @DueAmount = 101

-- 10% off purchases over 100$ promotion

IF @DueAmount > 100

BEGIN

SET @DueAmount = @DueAmount * 0.9

END

SELECT @DueAmount

If you pay attention how the value is being set inside the if logic you will understand how compound assignments works. Here is the link to msdn page for more detailed info around setting the values to variables. It does help with less coding in your T-SQL but if there is sql server 2005 or earlier versions exist in your environment be careful to remember which environments that statement will be used.

HTH,

Bulent