Microsoft® SQL Server® 2012 Bible

396

Part IV: Programming with T-SQL

Incrementing Variables

T-SQL has the increment variable feature, which saves a few keystrokes when coding and

certainly looks cleaner and more modern.

The basic idea is that an operation and equals sign will perform that function on the vari-

able. For example, the code

SET @x += 5;

is the logical equivalent of

SET @x = @x + 5

The next short script walks through addition, subtraction, and multiplication using the

new variable increment feature:

DECLARE @x INT = 1

SET @x += 5

SELECT @x

SET @x -=3

SELECT @x

SET @x *= 2

SELECT @x

Result (of whole batch):

-----------

6

-----------

3

-----------

6

Conditional Select

Because the SELECT statement includes a WHERE clause, the following syntax works well,

although those not familiar with it may be confused:

SELECT @Variable = expression WHERE BooleanExpression;

The WHERE clause functions as a conditional IF statement. If the boolean expression is

true, then the SELECT takes place. If not, the SELECT is performed, but the @Variable is

not altered in any way because the SELECT command has no effect.

c16.indd 396c16.indd 396 7/30/2012 5:38:06 PM7/30/2012 5:38:06 PM

http://www.it-ebooks.info