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 replies
  1. Kent Waldrop
    Kent Waldrop says:

    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

    Reply
  2. Anonymous
    Anonymous says:

    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'.

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published.