Katmai: Variable Initialization and Assignment Operators - The Zen of Database Development - Site Home

It’s again.

Today I’m going to go over two new enhancements related to TSQL variables and how they acquire their values.

TSQL Variable Declaration and Initialization

In SQL 2008, the syntax by which TSQL variables may be declared has been enhanced. It is now possible to initialize variables during the DECLARE statement. This initialization uses virtually the same syntax as initializing variables using a SET statement. So while in SQL 2005, initialization might look like

DECLARE @i INT, @j INT, @k INT
SET @i = 10
SET @j = 11
SET @k = SELECT MIN(OrderID) from Orders

In Katmai, this can be all done in one statement

DECLARE @i INT = 10, @j INT = 11, @k INT = (SELECT MIN(OrderID) from Orders)

Katmai does not allow you to use a variable in the initialization of another variable in the same DECLARE statement. Because there is no enforced order of precedence in the processing, this syntax is not allowed:

DECLARE @i INT = 10, @j INT = 11, @k INT = (SELECT MIN(OrderID) + @i from Orders)

However, this is:

DECLARE @i INT = 10
DECLARE @j INT = @i + 1
DECLARE @k INT = (SELECT MIN(OrderID) + @i from Orders)

Note that Katmai still does not permit mixing Scalar declarations (those that declare a TSQL variable capable of holding a single value, such as above) with either Table Variable declarations or Cursor declarations.

Compound Assignment Operators

Katmai has added support for compound assignment operators to any statement where an assignment occurs on the right hand side of an expression. For those who may not be familiar with the term, Compound Assignment operators are used to update an existing value in place. For instance, the statement:

SET @result = @result + 1

Is functionally equivalent to the compound assignment statement:

SET @result += 1

The list of compound assignment operations that Katmai supports will be very familiar to anyone used to working in C# or other procedural languages.

Description	Syntax
Add and assign	+=
Subtract and assign	-=
Multiply and assign	*=
Divide and assign	/=
Modulo and assign	%=
Bitwise & and assign	&=
Bitwise \| and assign	\|=
Bitwise xor and assign	^=

This includes the ability to assign values to TSQL Variables using SET, as well as set values in UPDATE statements and set variables in SELECT statements.

Some examples:

SET @total += dbo.calculateInterest(@amount);

UPDATE Accounts
SET balance -= 200
WHERE accountId = @account_ID

UPDATE Accounts
SET @total = balance += dbo.calculateInterest(@amount)
WHERE accountId = @account_ID

These are some pretty simple features to experiment with. Next week, we'll get more ambitious, and I'll discuss the new Grouping Sets enhancement.

Until then!