Tuesday, April 8, 2008

Delighters in SQL Server 2008

Two of the new T-SQL enhancements in SQL Server 2008 are the inline variable initialization and compound assignment (also called delighters). We had those for a long time in languages like C, C++, C#, VB, and they are just coming to T-SQL. While not of significant value, it will make writing code easier and more compact.

Here are a couple examples to illustrate their use.

-- Declare and initialize variable

DECLARE @count INT = 1;

 

-- Compound assignments

SET @count += 1;

SET @count /= 2;

SET @count *= 5;

SET @count %= 3;

SET @count -= 1;

 

SELECT @count;

 

/*

 

-- Result

1

 

*/

 

-- Create sample table for employee pay rates

CREATE TABLE PayRates (

 employee_nbr INT PRIMARY KEY,

 pay_rate DECIMAL(15, 2),

 performance_score INT);

 

-- Insert data using the new row constructors

INSERT INTO PayRates VALUES (1, 40.00, 5), (2, 45.50, 4), (3, 39.50, 6);

 

-- Apply pay rate increase

-- Compound assignments can be used with columns on the right side

UPDATE PayRates

SET pay_rate *= performance_score * 0.25;

 

SELECT employee_nbr, pay_rate, performance_score

FROM PayRates;

 

-- Declare variable and initialize using query

DECLARE @max_pay_rate DECIMAL(15, 2) = (SELECT MAX(pay_rate)

                                        FROM PayRates);

 

SELECT @max_pay_rate AS max_pay_rate;

2 comments:

Kent Waldrop said...

In addition to the compound assignment operators listed, the '+=' operator also works for concatenation and there are compound assignment operators for the bit operators:

&= for 'and'
|= for 'or'
^= for 'exclusive or'

I don't know how useful these are but I tested them to make sure they function in SQL

Kent Waldrop

Anonymous said...

Hi,

This doesn't seem to work in SSMS 2008. I get the following error:


---------------------------------------
Msg 139, Level 15, State 1, Line 3
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 21
Must declare the variable '@count'.